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

tsqlLine number On/Off | Show/Hide | Select all

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

tsqlLine number On/Off | Show/Hide | Select all

USE northwind
GO
DECLARE @intObjectID INT
SELECT @intObjectID =OBJECT_ID('orders')
SELECT COALESCE(OBJECTPROPERTY(@intObjectID, 'TableHasIdentity'),0) AS HasIdentity