Find Out If A Table Has An Identity Column
From Wiki
Here are 2 ways to find out if a table has an identity column The first way is using the COLUMNPROPERTY function and the second way is using the OBJECTPROPERTY function
Use COLUMNPROPERTY and the syscolumns system table
- USE northwind
- GO
- DECLARE @tableName VARCHAR(50)
- SELECT @tableName = 'orders'
- SELECT COUNT(name) AS HasIdentity
- FROM syscolumns
- WHERE OBJECT_NAME(id) = @tableName
- AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
- GO
Use OBJECTPROPERTY and the TableHasIdentity property name
- USE northwind
- GO
- DECLARE @intObjectID INT
- SELECT @intObjectID =OBJECT_ID('orders')
- SELECT COALESCE(OBJECTPROPERTY(@intObjectID, 'TableHasIdentity'),0) AS HasIdentity
Contributed by: --SQLDenis 20:46, 29 May 2008 (GMT)
Part of SQL Server Programming Hacks


