Systems Engineering and RDBMS

Listing all the tables with identity columns in the schema

Posted by decipherinfosys on March 4, 2007

Here is a small useful script to list out all the tables that have identity columns, their seed values and their increment values (works for both SQL Server 2000 as well as SQL Server 2005):

SELECT
IDENT_SEED(TABLE_NAME) AS Seed
, IDENT_INCR(TABLE_NAME) AS Increment
, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), ‘TableHasIdentity’) = 1
AND TABLE_TYPE = ‘BASE TABLE’

The same thing can be done in SQL Server 2005 in a different way as well:

select a.seed_value, a.increment_value, object_name(a.object_id) as table_name, a.name as column_name
from sys.identity_columns a
inner join sys.objects b
on a.object_id = b.object_id
where b.type = ‘u’
order by object_name(a.object_id)

Sorry, the comment form is closed at this time.

 
%d bloggers like this: