By Jack


2015-06-30 16:15:15 8 Comments

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

2 comments

@Aaron Bertrand 2015-06-30 16:43:33

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;

@Thronk 2015-06-30 16:37:18

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

Related Questions

Sponsored Content

1 Answered Questions

[SOLVED] How can I reseed all identity columns?

5 Answered Questions

[SOLVED] Identity column as clustered index bad idea?

2 Answered Questions

[SOLVED] Stored Procedure - Reseed Identity

2 Answered Questions

[SOLVED] How to remove identity specification for multiple tables

1 Answered Questions

[SOLVED] Restore all IDENTITY seeds (out of sync since database restore)

2 Answered Questions

[SOLVED] Changing identity column from INT to BIGINT

1 Answered Questions

1 Answered Questions

[SOLVED] Why is removing the Identity property on a column not supported

1 Answered Questions

[SOLVED] Get identity value of all rows inserted

Sponsored Content