Script to check current identity of Tables

,

Change output to text and excute script on User database to display current identity for all user tables.

SET NOCOUNT ON



DECLARE @VtblCodeID TABLE(CodeID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED

 ,Code NVARCHAR(500))

INSERT INTO @VtblCodeID

SELECT 'PRINT ''' + o.name + '''' + CHAR(13) +

 'SELECT IDENT_CURRENT(''' + o.name + ''')'

FROM sysobjects o JOIN syscolumns c ON o.ID = c.ID

WHERE objectproperty(o.id, 'IsMSShipped') = 0

AND objectproperty(o.id, 'IsTable') = 1

AND objectproperty(o.id, 'TableHasIdentity') = 1

AND columnproperty(c.id, c.name, 'IsIdentity') = 1



DECLARE @VinCounter INT

 ,@VinCounterMax INT

 ,@CODE NVARCHAR(500)

 ,@SQL NVARCHAR(500)



SELECT @VinCounter = 1

 ,@VinCounterMax = MAX(CodeID)

FROM @VtblCodeID

--LOOP THROUGH EACH CODE RECORD



--SELECT * from @VtblCodeID



WHILE (@VinCounter <=@VinCounterMax)

BEGIN

 SELECT @SQL = Code

 FROM @VtblCodeID

 WHERE CodeID = @VinCounter



 EXEC (@SQL)



 SET @VinCounter = @VinCounter + 1

END

Rate

2.67 (3)

Share

Share

Rate

2.67 (3)