List out all IDENTITY Columns in a database

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 1199
Sometimes, we will suppose to find out the tables which have IDENTITY column...

Below is the code to get the all IDENTITY column tables in a database
SELECT 
 
[schema] = OBJECT_SCHEMA_NAME([object_id]),
 
[table]  = OBJECT_NAME([object_id])
FROM sys
.identity_columns;

Comments or Responses

Posted by: vishalneeraj-24503 on: 7/9/2014 Level:Platinum | Status: [Member] [MVP] | Points: 10
Hi bandi,

I ran your code and it's giving only table names which have identity columns. Could you please also let us know identity columns,so that it would be nice to see all the identity column names.
Posted by: Bandi on: 7/10/2014 Level:Platinum | Status: [Member] [MVP] | Points: 10
use the below code for listing out the identity columns along with the table names...

SELECT 
 
[schema] = OBJECT_SCHEMA_NAME([object_id]),
 
[table]  = OBJECT_NAME([object_id]),
 
[Column Name] = name
FROM sys
.identity_columns;

Posted by: Chita_086 on: 8/26/2014 Level:Starter | Status: [Member] | Points: 10
yes it will work



thanks,
chitaranjan

Login to post response