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