This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am sharing a script to find all identity columns of a SQL Server Database.
We are using identity for generating auto_number in SQL Server table, and this is also one of the standard practice for all SQL Server Database Developers. This script will help us to find the list of identity columns of a Database.
Below are different scripts:
Using sys.objects and sys.identity_columns:
|
1 2 3 4 5 6 7 8 9 |
SELECT T.Name AS TableName ,IC.name AS ColumnName ,IC.is_identity AS IsIdentity FROM sys.objects AS T INNER JOIN sys.identity_columns AS IC ON T.[object_id]=IC.[object_id] WHERE T.type='U' AND is_identity=1 |
Using sys.tables and sys.identity_columns:
|
1 2 3 4 5 6 7 8 9 |
SELECT T.Name AS TableName ,IC.name AS ColumnName ,IC.is_identity AS IsIdentity FROM sys.tables AS T INNER JOIN sys.identity_columns AS IC ON T.[object_id]=IC.[object_id] WHERE T.type='U' AND is_identity=1 |
Using INFORMATION_SCHEMA.COLUMNS:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
;With CTE AS ( SELECT Table_Schema+'.'+Table_Name AS TableName ,[Column_name] AS ColumnName FROM INFORMATION_SCHEMA.COLUMNS ) SELECT TableName ,ColumnName ,COLUMNPROPERTY(OBJECT_ID(TableName),ColumnName,'IsIdentity') AS IsIdentity FROM CTE WHERE COLUMNPROPERTY(OBJECT_ID(TableName),ColumnName,'IsIdentity')=1 |
