This script will provide a comprehensive count of records for each table in the database, considering different schemas. The results are displayed in descending order of record count, which can be useful for identifying the largest tables in terms of data volume.

DYNQ_Count_Records_in_Each_Table_with_Schema.sql

-- Declare variables to hold table information
DECLARE @SchemaName NVARCHAR(128);
DECLARE @TableName NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);

-- Create a table to store the results
IF OBJECT_ID('tempdb..#TableRecordCounts') IS NOT NULL
    DROP TABLE #TableRecordCounts;

CREATE TABLE #TableRecordCounts (
    SchemaName NVARCHAR(128),
    TableName NVARCHAR(128),
    RecordCount BIGINT
);

-- Cursor to iterate over all tables in the database
DECLARE table_cursor CURSOR FOR
SELECT 
    s.name AS SchemaName,
    t.name AS TableName
FROM 
    sys.tables AS t
    JOIN sys.schemas AS s ON t.schema_id = s.schema_id;

OPEN table_cursor;
FETCH NEXT FROM table_cursor INTO @SchemaName, @TableName;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Construct the dynamic SQL to count the records in the current table
    SET @sql = N'SELECT @RecordCount = COUNT(*) FROM [' + @SchemaName + N'].[' + @TableName + N'];';

    -- Declare a variable to hold the record count
    DECLARE @RecordCount BIGINT;

    -- Execute the dynamic SQL
    EXEC sp_executesql @sql, N'@RecordCount BIGINT OUTPUT', @RecordCount OUTPUT;

    -- Insert the result into the temporary table
    INSERT INTO #TableRecordCounts (SchemaName, TableName, RecordCount)
    VALUES (@SchemaName, @TableName, @RecordCount);

    -- Fetch the next table
    FETCH NEXT FROM table_cursor INTO @SchemaName, @TableName;
END

-- Close and deallocate the cursor
CLOSE table_cursor;
DEALLOCATE table_cursor;

-- Select the results
SELECT * FROM #TableRecordCounts
ORDER BY RecordCount DESC;

-- Drop the temporary table
DROP TABLE #TableRecordCounts;

Certainly! Here is a complete T-SQL script to find the count of records in each table within a SQL Server database, considering that tables might belong to different schemas.

Title:

"Script to Count Records in Each Table with Schema in SQL Server"

Explanation:

1. Variable Declarations:

   • Variables @SchemaName and @TableName are declared to hold schema and table names during cursor iteration.
   • A variable @sql is declared to construct dynamic SQL statements.

2. Result Table:

   • A temporary table #TableRecordCounts is created to store the schema name, table name, and record count.

3. Cursor Declaration and Open:

   • A cursor table_cursor is declared and opened to iterate over all tables in the database. This includes joining sys.tables and sys.schemas to get schema names.

4. Dynamic SQL Construction and Execution:

   • Inside the loop, a dynamic SQL statement is constructed to count the records in the current table.
   • The dynamic SQL statement is executed using sp_executesql, and the record count is captured in the @RecordCount variable.

5. Insert Results:

   • The schema name, table name, and record count are inserted into the #TableRecordCounts temporary table.

6. Cursor Cleanup:

   • After processing all tables, the cursor is closed and deallocated to release resources.

7. Select Results:

   • The results are selected from the #TableRecordCounts table and displayed in descending order of record count.

8. Cleanup:

   • The temporary table #TableRecordCounts is dropped to clean up resources.

This script will provide a comprehensive count of records for each table in the database, considering different schemas. The results are displayed in descending order of record count, which can be useful for identifying the largest tables in terms of data volume.