Display Identity Values and Other Info

,

Displays identity 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.

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

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO


CREATE PROC hx_IdentityColumnValues
AS

/*
	Robert Vallee rvallee@hybridx.com
	09/06/2001
	* Modified hx_ShowIdentityColumns stored procedure.
	input: none
	Description: Displays identity 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

Rate

Share

Share

Rate