Return tables which has primary key but not set Identity Specification or (Is Identity)

Question

I need to write a query that will return all the tables which have a Primary key column but not set Identity Specification or (Is Identity) to Yes.

Can anyone please help me

Answer 1

Not the cleanest but it should work.

SELECT cu.TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE cu
INNER JOIN sys.Tables t ON cu.TABLE_NAME = t.Name
INNER JOIN sys.Columns c ON t.object_id = c.object_id AND cu.COLUMN_NAME = c.Name
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND is_identity = 0