question

Katie 1 avatar image
Katie 1 asked

Finding if identity is on any tables in the database

Guys

Is there any way to findout which tables have identity property set ON on a table in the database.

There are about 300 databases and to check manaually if the property is on.. is becoming a time consuming process. Is there any system table that would give this information?

Thanks.

sql-server-2008t-sqladministrationdatabase-design
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
CirqueDeSQLeil avatar image
CirqueDeSQLeil answered

You could set up a script that utilizes sp_msforeachdb and execute the following:

select OBJECT_name(object_id) as TableName,name as ColumnName
    from sys.columns
    Where is_identity = 1

Or, use an SSIS package to do the same.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.