There are times that you may find yourself writing some bulk insert statements from one table to another. However, some tables may have an IDENTITY column while other tables may not have an IDENTITY column. You can always right click on the table in question, open up the table designer and see if a table has a IDENTITY specification set to yes. You’ll have to check all of the columns, of course, since the first column may not be the IDENTITY column. But you really can’t automate right clicking and searching. Luckily, you can count on SQL Dynamic Management Views or DMV to give you the help you need. The code below will identify if a target table has an identity column along with the name of the identity column using sys.objects and sys.columns.
SELECT sc.name
FROM sys.objects so, sys.columns sc
WHERE so.object_id = sc.object_id
AND sc.is_identity = 1
AND so.name = 'Table_Name'
Furthermore, you can use IS NOT NULL and control your program flow like this:
IF (
SELECT sc.name
FROM sys.objects so, sys.columns sc
WHERE so.object_id = sc.object_id
AND sc.is_identity = 1
AND so.name = 'Table_Name'
) IS NOT NULL
BEGIN
-- Do something
END
ELSE
BEGIN
-- Do something else
END
