Archive

Posts Tagged ‘get count of all tables in sql server’

T-SQL : Get count of all tables in a database

The following query will list the count of records in all tables of a database. These queries are based on CURSORS,

Query 1:
Query to get individual results,

DECLARE @SQL        VARCHAR( max ),
        @TableName  VARCHAR (255 ),
        @SchemaName VARCHAR (50 ) = 'dbo'
DECLARE TableCount CURSOR FOR
  SELECT table_name
  FROM   INFORMATION_SCHEMA .tables
  WHERE  table_schema = @SchemaName
OPEN TableCount
FETCH next FROM TableCount INTO @TableName
WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @SQL = 'select ''' + @TableName
                 + ''' as TableName,count(*) [Count] from '
                 + @SchemaName + '.' + @TableName
      PRINT @SQL
      FETCH next FROM TableCount INTO @TableName
      EXEC (@SQL )
  END
CLOSE TableCount
DEALLOCATE TableCount

Query 2:
Results stored in singe temp table,

DECLARE @SQL        VARCHAR( max ),
        @TableName  VARCHAR (255 ),
        @SchemaName VARCHAR (50 ) = 'dbo'

CREATE TABLE #TableCount
  (
     TableName  VARCHAR (255 ),
     TableCount INT
  )

DECLARE TableCount CURSOR FOR
  SELECT table_name
  FROM   INFORMATION_SCHEMA .tables
  WHERE  table_schema = @SchemaName

OPEN TableCount
FETCH next FROM TableCount INTO @TableName
WHILE @@FETCH_STATUS = 0
  BEGIN
      SET @SQL = 'insert into #TableCount
      select ''' + @TableName
                 + ''' as TableName,count(*) [Count] from '
                 + @SchemaName + '.' + @TableName
      PRINT @SQL
      FETCH next FROM TableCount INTO @TableName
      EXEC (@SQL )
  END
CLOSE TableCount
DEALLOCATE TableCount
SELECT *
FROM    #tableCount
DROP TABLE #tableCount

Happy Querying 😉

Advertisements
%d bloggers like this: