7

I need to write a query that resets the identity on all tables that have an identity column and are empty.

11

Since you can reset the IDENTITY by issuing a simple TRUNCATE:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'TRUNCATE TABLE ' + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + N';' + CHAR(13) + CHAR(10)
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.identity_columns AS ic
ON t.[object_id] = ic.[object_id]
INNER JOIN sys.partitions AS p
ON p.[object_id] = t.[object_id]
WHERE p.[rows] = 0 -- only empty tables
AND p.index_id IN (0,1)
AND p.partition_number = 1;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Though this will only work for tables not referenced by a foreign key. If you have foreign keys referencing these tables (likely), then you need:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'DBCC CHECKIDENT(N''' + QUOTENAME(s.name) + N'.'
  + QUOTENAME(t.name) + N''', RESEED);' + CHAR(13) + CHAR(10)
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.identity_columns AS ic
ON t.[object_id] = ic.[object_id]
INNER JOIN sys.partitions AS p
ON p.[object_id] = t.[object_id]
WHERE p.[rows] = 0 -- only empty tables
AND p.index_id IN (0,1)
AND p.partition_number = 1;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;
3

You can write a script to call DBCC CHECKIDENT ([TABLENAME], RESEED, 0) over the tables resulting from this query (which will inform of all the tables with an identity field.

SELECT         
               t.TABLE_NAME
              ,c.COLUMN_NAME
              ,c.TABLE_CATALOG
              ,c.TABLE_SCHEMA 
FROM         
              INFORMATION_SCHEMA.COLUMNS AS c JOIN
              INFORMATION_SCHEMA.TABLES AS t
                      ON t.TABLE_NAME = c.TABLE_NAME
WHERE         
               COLUMNPROPERTY(OBJECT_ID(c.TABLE_NAME)
                      ,c.COLUMN_NAME,'IsIdentity') = 1 AND
               t.TABLE_TYPE = 'Base Table' AND
               t.TABLE_NAME NOT LIKE 'dt%' AND
               t.TABLE_NAME NOT LIKE 'MS%' AND
               t.TABLE_NAME NOT LIKE 'syncobj_%' 

Thanks to http://weblogs.sqlteam.com/joew/archive/2008/06/27/60641.aspx

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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