Tuesday, March 14, 2006

Find Out If A Table Has An Identity Column

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 northwind

DECLARE @tableName VARCHAR(50)
SELECT @tableName = 'orders'

--Use COLUMNPROPERTY and the syscolumns system table
SELECT COUNT(name) AS HasIdentity
FROM syscolumns
WHERE OBJECT_NAME(id) = @tableName
AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1

SELECT @intObjectID =OBJECT_ID('orders')

--Use OBJECTPROPERTY and the TableHasIdentity property name
SELECT COALESCE(OBJECTPROPERTY(@intObjectID, 'TableHasIdentity'),0) AS HasIdentity

No comments: