How to get Record Count for all the tables in SQL Server Instance in SQL Server - SQL Server Tutorial

Scenario:

You are working as SQL Server DBA or Developer, You need to write script that should get you record count for all the tables in all the database in SQL Server Instances.


Solution:

As we have to get row count for tables in all the databases, we will be using Cursor to loop through database names and get record count for all the tables in each database. We will insert the information in temp table and then finally we will query at the end to see the records. 

USE master
GO

--Drop Temp table if exists to save record count for all the tables on SQL Server Instance
IF OBJECT_ID('tempdb..##RecordCount') IS NOT NULL
    DROP TABLE ##RecordCount

CREATE TABLE ##RecordCount (
    [ServerName] VARCHAR(128)
    ,[DatabaseName] VARCHAR(128)
    ,[SchemaName] VARCHAR(128)
    ,[TableName] VARCHAR(128)
    ,RecordCnt BIGINT
    )

--Use Cursor to Loop through Databases
DECLARE @DatabaseName AS VARCHAR(500)

DECLARE Cur CURSOR
FOR
SELECT NAME
FROM sys.databases
WHERE database_id > 4

OPEN Cur

FETCH NEXT
FROM Cur
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @DBName AS NVARCHAR(500);

    SET @DBName = QUOTENAME(N'' + @DatabaseName + '');

    --USE Dynamic SQL to get record count for all tables from each database
    EXEC (
            N'USE ' + @DBName + N'; EXEC(''
Insert into ##RecordCount
Select @@ServerName AS ServerName,DB_Name() AS DatabaseName,
Schema_name(schema_id) as SchemaName,t.name,s.rows
 from sysindexes s
inner join sys.tables t
on s.id=t.object_id
WHERE  s.indid IN ( 0, 1, 255 )
       AND is_ms_shipped = 0
'');'
            );

    FETCH NEXT
    FROM Cur
    INTO @DatabaseName
END

CLOSE Cur

DEALLOCATE Cur

--Return ServerName,DatabaseName,SchemaName,TableName and Record Count from Temp Table
SELECT *
FROM ##RecordCount


I execute above script on my SQL Server Instance and it returned me Server Name, Database Name, Schema Name, Table Name and Row Count.
How to get Row Count for all the tables in SQL Server Instance - SQL Server Tutorial

No comments:

Post a Comment