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
![]() |
How to get Row Count for all the tables in SQL Server Instance - SQL Server Tutorial |
No comments:
Post a Comment