Here’s a T-SQL find all identity columns in a database.

The sys.columns view lists column information for tables/views/table valued functions. If a column is an identity column then sys.columns.is_identity is 1 else it is 0. This is how we can list all identity columns for all tables in a database. The output from above query is shown below.

1_t sql find all identity columns

Another way to get this information is from sys.identity_columns view.  It contains details of all identity columns in a database along with their seed value, increment value and lots other information. The query for the same is given below.

The output from the above query is shown below.

2_t sql find all identity columns

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook