Tuesday, May 27, 2014

How To Get Record count for each table in database

For the purpose of checking for valuable space on database we may need to know which tables are not in use in recent times.
  So here is simple script that will extract no of records in each table of database.

We all know that sysobjects tables hold list of object in database and user defined tables have xtype as ‘u’.

So we can retrive list of all tables as

select * from sys.sysobjects where xtype='u'

we first create a temp table to hold our output and then using cursor
we will find no of records in each table & insert the required data into our
output table.


create table #Tmp
(
      TableName varchar(200),
      RecCount bigint,
      DbScanDate datetime
)


DECLARE @TableName varchar(200)
DECLARE @sql nvarchar(MAX)
DECLARE @RecCount int

DECLARE @TableListCursor CURSOR
SET @TableListCursor = CURSOR FOR
select name from sys.sysobjects where xtype='u'

OPEN @TableListCursor

FETCH NEXT
FROM @TableListCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
      --PRINT @TableName
      set @sql =N'select @RecCount=COUNT(*) from ' + @TableName
      exec sp_executesql @sql, N'@RecCount int out', @RecCount out
     
      insert into #Tmp
      (
            TableName,
            RecCount,
            DbScanDate
      )
      values
      (
            @TableName,
            @RecCount,
            GETDATE()
      )
     
      set @RecCount=0
      set @TableName=''
     
      FETCH NEXT
      FROM @TableListCursor INTO @TableName
END
CLOSE @TableListCursor
DEALLOCATE @TableListCursor

select * from #Tmp order by RecCount asc

drop table #Tmp


Thankx & Happy Coding!

No comments:

Post a Comment