count_rows.sql

-- Declare a temporary table to store the counts
CREATE TABLE #RowCounts (
    TableName NVARCHAR(256),
    RowCount1 INT
);

-- Declare variables
DECLARE @TableName NVARCHAR(256);
DECLARE @Sql NVARCHAR(MAX);
DECLARE @RowCount INT;

-- Cursor to iterate through user-created tables
DECLARE TableCursor CURSOR FOR 
SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = DB_NAME();

-- Open cursor
OPEN TableCursor;

-- Fetch the first table
FETCH NEXT FROM TableCursor INTO @TableName;

-- Iterate through the tables
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Check if the table exists and is accessible
    IF OBJECT_ID(@TableName) IS NOT NULL
    BEGIN
        -- Prepare the dynamic SQL to get the row count
        SET @Sql = N'SELECT @RowCountOUT = COUNT(*) FROM ' + @TableName;

        -- Execute the dynamic SQL
        EXEC sp_executesql @Sql, N'@RowCountOUT INT OUTPUT', @RowCountOUT = @RowCount OUTPUT;

        -- Insert the row count into the temporary table
        INSERT INTO #RowCounts (TableName, RowCount1) VALUES (@TableName, @RowCount);
    END

    -- Fetch the next table
    FETCH NEXT FROM TableCursor INTO @TableName;
END

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

-- Select the results
SELECT * FROM #RowCounts;

-- Drop the temporary table
DROP TABLE #RowCounts;