Pretty helpful... and necessary, if and when you're moving a lot of data around. This is a real quick checker for which tables were defined with the IDENTITY property:
-- Find IDENTITIES v2008
SELECT
OBJECT_NAME(OBJECT_ID) AS TABLENAME,
NAME AS COLUMNNAME,
SEED_VALUE,
INCREMENT_VALUE,
LAST_VALUE,
IS_NOT_FOR_REPLICATION
FROM
SYS.IDENTITY_COLUMNS
ORDER BY
tablename
--Find IDENTITIES v2000
SELECT
'TableName' = o.name,
'ColumnName' = c.name
FROM
sysobjects o INNER JOIN syscolumns c ON o.id = c.id
WHERE
c.status = 128
AND o.type = 'u'
AND o.status > 0
ORDER BY
o.name
Even better, here's one that I put together very recently using sys.identity_columns, which returns a row for each column in your database that is an IDENTITY column. I joined it to sys.sysobjects to give you a little more detail, by including the table names as well:
SELECT
so.name [TableName],
ic.name [ColumnName],
ic.seed_value [Seed],
ic.increment_value [Increment],
ic.last_value [LastValue]
FROM
sys.identity_columns ic JOIN sys.sysobjects so
ON ic.object_id = so.id
WHERE
so.name <> 'sysdiagrams'
AND so.name NOT LIKE 'queue_%'
ORDER BY
so.name
No comments:
Post a Comment