需要檢查 total 有多少個 table 及裡面的資料筆數

CREATE TABLE ##TempResult (TABLE_NAME NVARCHAR(255), ROW_COUNT INT)

DECLARE @tableName NVARCHAR(255)

DECLARE tableCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tableCursor

FETCH NEXT FROM tableCursor INTO @tableName

WHILE @@FETCH_STATUS = 0
BEGIN
    DECLARE @sql NVARCHAR(MAX)
    SET @sql = 'INSERT INTO ##TempResult (TABLE_NAME, ROW_COUNT) SELECT ''' + @tableName + ''', COUNT(*) FROM ' + @tableName
    EXEC sp_executesql @sql

    FETCH NEXT FROM tableCursor INTO @tableName
END

CLOSE tableCursor
DEALLOCATE tableCursor

-- Retrieve distinct results
SELECT DISTINCT TABLE_NAME, ROW_COUNT FROM ##TempResult order by ROW_COUNT desc

-- Drop the temporary table
DROP TABLE ##TempResult