Displays Identity Column Values

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hx_IdentityColumnAdvanced]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[hx_IdentityColumnAdvanced]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROC hx_IdentityColumnAdvanced
AS

/*
	Robert Vallee rvallee@hybridx.com
	09/06/2001
	* Modified hx_ShowIdentityColumns stored procedure.
	input: none
	Description: Displays identity column values, number of rows in the table, and the difference between the two.	
	The difference can indicate the amount of deletions that could be going on.
	Warnings: None


*/

SET NOCOUNT ON
SET ANSI_WARNINGS OFF
CREATE TABLE #ts1(
[Table_Name] varchar(70),
[Column_Name] varchar(75),
[Largest_Identity_Value] int,
[#_of_Rows] int,
[Difference] int
)

DECLARE @tbl varchar(50)
DECLARE @col varchar(75)
DECLARE @str varchar(2000)

DECLARE IdentValue CURSOR SCROLL KEYSET FOR 

select o.name as [Table_Name], 
c.name as [Column_Name]
from syscolumns c, sysobjects o
where c.id=o.id and (c.status & 128)=128

OPEN IdentValue

FETCH FIRST FROM IdentValue INTO @tbl,@col

	WHILE @@FETCH_STATUS = 0
	BEGIN 	
	
	SET @str = 'select ' + '''' + @tbl + '''' + ' as [Table], ' + '''' + @col + '''' + ' as [Column_Name], ' + 
	'(select max(' + @col + ') as [Largest_Identity_Value] from ' + @tbl + '), 
	 (select count(' + @col + ')  as [#_of_Rows] from ' + @tbl + '),
	 (select max(' + @col + ') from ' + @tbl + ') -  (select count(' + @col + ') from ' + @tbl + ') as [Difference]'

		
FETCH NEXT FROM IdentValue INTO @tbl,@col
	IF @@FETCH_STATUS = 0
	BEGIN

	INSERT INTO #ts1
	exec (@str)

	END
		END
			
	CLOSE IdentValue
	DEALLOCATE IdentValue 

SET ANSI_NULLS OFF
SELECT Table_Name,Column_Name,
'Largest_Identity_Value  ' = case
WHEN [Largest_Identity_Value] = NULL THEN 0
ELSE [Largest_Identity_Value]
END,
'#_of_Rows' = case
WHEN [#_of_Rows] = NULL THEN 0
ELSE [#_of_Rows]
END,
'Difference' = case
WHEN [Difference] = NULL THEN 0
ELSE [Difference]
END
FROM #ts1
order by [Table_Name]

drop table #ts1

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO