แสดงจำนวนแถวของแต่ละตารางใน DB

----------------------------------------------------------------------------------------------------------------------------------------------
แบบที่ 1 จะเป็นการสร้างคิวรี่ให้กับแต่ละตาราง แล้วรันคิวรี่นั้นอีกที
----------------------------------------------------------------------------------------------------------------------------------------------
SELECT 'select '''+ name +''', count(*) from ' + name + ' union'
FROM Sysobjects
WHERE Type='U'
ORDER BY 1;

----------------------------------------------------------------------------------------------------------------------------------------------
แบบที่ 2 ใช้ Cursor แสดงข้อมูลออกมาม้วนเดียวจบและเรียงตามลำดับของจำนวนแถวคับ
----------------------------------------------------------------------------------------------------------------------------------------------

-- Create a cursor to loop through the System Ojects and get each table name
DECLARE TBL_CURSOR CURSOR
-- Declare the SQL Statement to cursor through
FOR ( SELECT Name FROM Sysobjects WHERE Type='U' )
-- Declare the @SQL Variable which will hold our dynamic sql
DECLARE @SQL NVARCHAR(MAX);
SET @SQL = '';
-- Declare the @TblName Variable which will hold the name of the current table
DECLARE @TblName NVARCHAR(MAX);
-- Open the Cursor
OPEN TBL_CURSOR
-- Setup the Fetch While that will loop through our cursor and set @TblName
FETCH NEXT FROM TBL_CURSOR INTO @TblName
-- Do this while we are not at the end of the record set
WHILE (@@FETCH_STATUS <> -1)
BEGIN
-- Appeand this table's select count statement to our sql variable
--SET @SQL = @SQL + ' ( SELECT '''+@TblName+''' AS Table_Name,COUNT(*) AS Count FROM '+@TblName+' ) UNION';
SET @SQL = @SQL + ' ( SELECT '''+@TblName+''' AS Table_Name,COUNT(*) AS Count FROM ['+@TblName+'] ) UNION';
-- Pull the next record
FETCH NEXT FROM TBL_CURSOR INTO @TblName
-- End the Cursor Loop
END
-- Close and Clean Up the Cursor
CLOSE TBL_CURSOR
DEALLOCATE TBL_CURSOR
-- Since we were adding the UNION at the end of each part, the last query will have
-- an extra UNION. Lets trim it off.
SET @SQL = LEFT(@SQL,LEN(@SQL)-6);
-- Lets do an Order By. You can pick between Count and Table Name by picking which
-- line to execute below.
SET @SQL = @SQL + ' ORDER BY Count';
--SET @SQL = @SQL + ' ORDER BY Table_Name';

-- Now that our Dynamic SQL statement is ready, lets execute it.
EXEC (@SQL);
GO
----------------------------------------------------------------------------------------------------------------------------------------------