2

This question already has an answer here:

I wanted to RESEED all the identity columns in that database, so I need to create a script so I can find out which column in which table has the identity property.

marked as duplicate by Paul White Jan 27 '16 at 7:17

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

Using the script from the Question as a starting point, you might be better off using the following query:

SELECT
  N'DBCC CHECKIDENT (''' +
  SCHEMA_NAME(tbl.[schema_id]) +
  N'.' +
  tbl.name +
  N''', RESEED); -- WITH NO_INFOMSGS;'
   , clmn.seed_value
   , clmn.increment_value
   , clmn.last_value
FROM       sys.tables tbl 
INNER JOIN sys.identity_columns clmn
        ON clmn.[object_id] = tbl.[object_id]
WHERE  tbl.[object_id] <> OBJECT_ID(N'dbo.sysdiagrams') -- skip "dbo.sysdiagrams"
ORDER BY SCHEMA_NAME(tbl.[schema_id]), tbl.name;

The main difference is rather than determining the IDENTITY field by matching the column name found in sys.identity_columns, this query replaces the JOIN to sys.columns with sys.identity_columns and removes it from the WHERE clause. The effect of this is to implicitly filter the rows (since only tables with IDENTITY columns would be in sys.identity_columns) and to now have access to the IDENTITY meta-data: seed_value, increment_value, and last_value.

Matching on the column name only, you run the risk of selecting tables that might not have an IDENTITY column but do have a column that has the same name as one of the columns found in sys.identity_columns.

1

Community Wiki answer for content originally provided in the question by DnL

SELECT
    -- in case that reseed will not work and u need a specific number u can use this script
    -- just delete the ' before DBCC
    --'DECLARE @identity INT SELECT @identity= '+clmn.name+'
    --FROM '+SCHEMA_NAME(schema_id)+'.'+tbl.name+' PRINT @identity
    'DBCC CHECKIDENT ('''+SCHEMA_NAME(schema_id)+'.'+tbl.name+''', RESEED);'
    --SCHEMA_NAME(schema_id) AS schema_name,
    --tbl.name AS table_name,
    --clmn.name AS column_name
FROM 
    sys.tables AS tbl 
INNER JOIN 
    sys.columns clmn ON tbl.OBJECT_ID = clmn.OBJECT_ID 
WHERE 
    clmn.name IN (SELECT name FROM sys.identity_columns
                  WHERE [object_id] = tbl.[object_id])
ORDER BY 
    tbl.name;

Not the answer you're looking for? Browse other questions tagged or ask your own question.