Finding if identity is on any tables in the database

Question

question

Katie 1 asked May 13, '10

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-2008 t-sql administration database-design

10 |1200

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

Answer 1 · 2010-05-13T14:16:53Z

CirqueDeSQLeil answered May 13, '10

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

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

question