Find out Number of Objects and Tables row count on MSSQL Server

What this script does:

1. Creates temp tables in tempdb:

   • #object_summary — to hold counts of object types

   • #row_counts — to hold real row counts

   • #tables_to_process — to hold a list of tables to iterate

2. Loops through each database you own (excluding master, model, msdb, tempdb).

3. Counts:

   • Number of tables, views, stored procs, functions, triggers

   • Actual row counts per table (COUNT(*))

4. Inserts the results into these temp tables.

5. Prints out results (SELECT * FROM #object_summary and SELECT * FROM #row_counts).

---

🔴 What this script does NOT do:

• It does not create or alter any permanent tables in your databases.

• It does not update or delete any data in your user tables.

• It does not create any persisted objects (everything is in tempdb).

• It does not change any schema, indexes, or triggers.

---

📝 Important

When you finish running the script:

• The temp tables are automatically dropped when your session closes.

• Nothing remains in your user databases.

---

✅ This script is read-only with respect to your application data.
You can safely run it in production if you are okay with the overhead of COUNT(*) queries (which will scan each table fully).

 SET NOCOUNT ON;

-- Drop temp tables if they exist

IF OBJECT_ID('tempdb..#object_summary') IS NOT NULL DROP TABLE #object_summary;

IF OBJECT_ID('tempdb..#row_counts') IS NOT NULL DROP TABLE #row_counts;

IF OBJECT_ID('tempdb..#tables_to_process') IS NOT NULL DROP TABLE #tables_to_process;

-- Temp table for object counts

CREATE TABLE #object_summary (

    db_name SYSNAME,

    tables INT,

    views INT,

    procedures INT,

    functions INT,

    triggers INT

);

-- Temp table for real row counts

CREATE TABLE #row_counts (

    db_name SYSNAME,

    schema_name SYSNAME,

    table_name SYSNAME,

    row_count BIGINT

);

DECLARE @db SYSNAME, @schema_name SYSNAME, @table_name SYSNAME, @sql NVARCHAR(MAX);

-- Cursor for user databases

DECLARE db_cursor CURSOR FOR

SELECT name FROM sys.databases

WHERE name NOT IN ('master','model','msdb','tempdb') AND state_desc = 'ONLINE';

OPEN db_cursor;

FETCH NEXT FROM db_cursor INTO @db;

WHILE @@FETCH_STATUS = 0

BEGIN

    -- 1. Insert object counts

    SET @sql = '

    USE [' + @db + '];

    INSERT INTO #object_summary

    SELECT 

        ''' + @db + ''',

        (SELECT COUNT(*) FROM sys.objects WHERE type = ''U''),

        (SELECT COUNT(*) FROM sys.objects WHERE type = ''V''),

        (SELECT COUNT(*) FROM sys.objects WHERE type = ''P''),

        (SELECT COUNT(*) FROM sys.objects WHERE type IN (''FN'',''IF'',''TF'')),

        (SELECT COUNT(*) FROM sys.triggers WHERE parent_id > 0);

    ';

    EXEC sp_executesql @sql;

    -- 2. Build list of tables in the current DB

    IF OBJECT_ID('tempdb..#tables_to_process') IS NOT NULL DROP TABLE #tables_to_process;

    CREATE TABLE #tables_to_process (

        schema_name SYSNAME,

        table_name SYSNAME

    );

    SET @sql = '

    USE [' + @db + '];

    INSERT INTO #tables_to_process

    SELECT s.name, t.name

    FROM sys.tables t

    JOIN sys.schemas s ON t.schema_id = s.schema_id;

    ';

    EXEC sp_executesql @sql;

    -- Cursor over tables in current DB

    DECLARE table_cursor CURSOR FOR

    SELECT schema_name, table_name FROM #tables_to_process;

    OPEN table_cursor;

    FETCH NEXT FROM table_cursor INTO @schema_name, @table_name;

    WHILE @@FETCH_STATUS = 0

    BEGIN

        -- Build dynamic SQL to count rows and insert result

        SET @sql = '

        INSERT INTO #row_counts

        SELECT 

            ''' + @db + ''',

            ''' + @schema_name + ''',

            ''' + @table_name + ''',

            COUNT(*)

        FROM [' + @db + '].[' + @schema_name + '].[' + @table_name + '];

        ';

        EXEC sp_executesql @sql;

        FETCH NEXT FROM table_cursor INTO @schema_name, @table_name;

    END

    CLOSE table_cursor;

    DEALLOCATE table_cursor;

    FETCH NEXT FROM db_cursor INTO @db;

END

CLOSE db_cursor;

DEALLOCATE db_cursor;

-- Output object summary

PRINT '=== OBJECT COUNTS BY DATABASE ===';

SELECT * FROM #object_summary ORDER BY db_name;

-- Output real row counts

PRINT '=== TABLE ROW COUNTS ===';

SELECT * FROM #row_counts ORDER BY db_name, schema_name, table_name;