getting the value of the field if identity but not primary

Question

Using this query

SELECT C.COLUMN_NAME[name] 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS T 
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C 
ON T.CONSTRAINT_NAME = C.CONSTRAINT_NAME 
WHERE T.CONSTRAINT_TYPE = 'PRIMARY KEY' 
AND T.TABLE_NAME = 'table1'

it shows if the table has primarykey, but i want to modify this sql as if the table has no primarykey but it has an identity set as 1, return me a record so i can identify it

can you use DMVs like sys.columns, sys.tables, sys.indexes etc.? — PeterHe, Aug 06 '19 at 17:35

score 0 · Answer 1 · answered Aug 06 '19 at 17:42

This should do the trick:

SELECT COLUMN_NAME [name]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table1' 
    AND COLUMNPROPERTY(object_id(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

Adapted from this answer: https://stackoverflow.com/a/87993/3519440

getting the value of the field if identity but not primary

1 Answers1