Technical Article

Displays Identity Column Values

,

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.

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

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating