Search

Monday, May 11, 2015

List Table with Identity Column

You can use below queries to get the list of all the tables with Identity column:

SELECT OBJECT_NAME(ID) AS [Table], Name AS [Column]
FROM syscolumns WHERE COLUMNPROPERTY(ID, Name, 'IsIdentity') = 1

Or use can use below query also

SELECT OBJECT_NAME(ID) AS [Table], Name AS [Column]
FROM syscolumns WHERE STATUS = 0x80

You can use blow query to check whether the table has Identity column:

SELECT Name AS [Table], OBJECTPROPERTY(ID, 'TableHasIdentity') AS [Has_Identity]
FROM SysObjects WHERE xType = 'U'

No comments:

Post a Comment