Finding Identity Key Columns in SQL Server 2005 | SansSQL

Tuesday, May 26, 2009

Finding Identity Key Columns in SQL Server 2005

To find the Identity Key Cloumns in a particular database

SELECT Object_Name(Object_IDAS TableName,
Name AS ColumnName,
Seed_Value AS SeedValue,
Increment_Value AS IncrementValue,
ident_current(Object_Name(Object_ID)) AS CurrentValue,
Last_Value AS LastValue
FROM sys.identity_columns
Order by TableName

To find the Identity Key Cloumns in all databases

EXEC sp_msforeachdb 'Use ?

SELECT ''?'' AS DatabaseName, Object_Name(Object_ID) AS TableName,
name AS ColumnName,
Seed_Value AS SeedValue,
Increment_Value AS IncrementValue,
ident_current(Object_Name(Object_ID)) AS CurrentValue,
Last_Value AS LastValue
FROM sys.identity_columns
Order by TableName'

No comments:

Post a Comment