Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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_%'
- 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;
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement