Monday, December 6, 2010

Find your IDENTITY tables, sys.identity_columns

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

You will see I excluded some system objects in that WHERE clause.  Feel free to include them, if desired.  And, if you've got tables with IDENTITY values, you should definitely take a look at this:  http://www.sqlfingers.com/2011/02/missing-identity-values.html

No comments:

Post a Comment