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.
2001-08-22
728 reads
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