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!