Home / T-SQL / Reset Ident Column in Database
Reset Ident Field in a Database

Reset Ident Column in Database

This is a very handy little script to generate a script to reset the ident column of all your database tables. This can be handy after filling and truncating tables. This script uses a schema “ps”. Replace it with .dbo if needed.

SELECT '
GO
DBCC CHECKIDENT(''ps.' + TabelNaam  + ''',RESEED,0)'
 
FROM
-- LT: Linked Tables
(SELECT O.name AS TabelNaam
, S.name AS SchemaName
FROM sysobjects AS O
LEFT OUTER JOIN sys.schemas AS S ON O.uid = S.schema_id
WHERE O.type = 'u' AND O.uid=5
AND CHARINDEX('_',O.name)=0
--ORDER BY O.[name]
) AS LT
WHERE SchemaName = 'ps'
ORDER BY TabelNaam

 

Usefull to export the output to text in stead of grid.

After running the script, copy the result to a new window and run it!

 

Check Also

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint

Using Microsoft SQL Server, you cannot truncate or drop a table with a foreign key …

Geef een reactie

Het e-mailadres wordt niet gepubliceerd. Vereiste velden zijn gemarkeerd met *