Developer Forum

Does anyone know how to determine if a field is an Identity field in Code? I
have written a procedure in VB that creates data scripts. The user enters
the table name into the program and it creates a script of the data in that
table. I have added the SET IDENTITY_INSERT <tablename> ON to the script to
deal with the insert of identity data. However, the script fails if there is
no Identity field in the table. I would like the program to check the fields
to see if there is an Identity field, and only add the SET IDENTITY_INSERT to
the script if there is. Else, that line would be omitted from the script.

If you are on SQL 2005 or later, you can use the sys.columns catalog view,
e.g.,

Declare @TableName varchar(50);
Set @TableName = 'YourTableName';
If Exists(Select *
From sys.columns
Where is_identity = 1
And Object_Name(object_id) = @TableName)
Begin
Print @TableName + ' has identity column';
End
Else
Begin
Print @TableName + ' does not have identity column';
End

If you are on SQL 2000 (or if you need to support both SQL 2000 and SQL 2005
or later), you can use the syscolumns table and the ColumnProperty function,
e.g.,

Declare @TableName varchar(50);
Set @TableName = 'YourTableName';
If Exists(Select *
From syscolumns
Where ColumnProperty(id, name, 'IsIdentity') = 1
And Object_Name(id) = @TableName)
Begin
Print @TableName + ' has identity column';
End
Else
Begin
Print @TableName + ' does not have identity column';
End

Tom

SELECT o.name, c.name FROM syscolumns c, sysobjects o
WHERE c.id = o.id AND (c.status & 128) = 128


--------------------
SELECT IDENT_SEED(OBJECT_NAME(id)) AS seed,
IDENT_INCR(OBJECT_NAME(id)) AS incr,
OBJECT_NAME(id) AS tbl
FROM syscolumns
WHERE (status & 128) = 128