SQL Server/テーブル行数一覧

　SQL Serverで、特定のDB内のテーブルを一覧にし、それらの行数を出力するクエリです。

テーブル行数一覧 †

CREATE TABLE #ROWCOUNTS
(
 TNAME varchar(255),
 CNT bigint
)

DECLARE @name nvarchar(255)
DECLARE @sql nvarchar(255)

DECLARE TCUR CURSOR FOR
SELECT name FROM sysobjects WHERE type = N'U'
OPEN TCUR

FETCH NEXT FROM TCUR INTO @name
WHILE (@@fetch_status <> -1)
BEGIN

SET @sql = 'INSERT into #ROWCOUNTS SELECT ''' + @name
         + ''' AS TNAME, count(*) as CNT FROM '+ @name
EXEC(@sql)

FETCH NEXT FROM TCUR INTO @name
END

CLOSE TCUR
DEALLOCATE TCUR

select sum(CNT) as row_count, count(*) as table_count from #ROWCOUNTS
select * from #ROWCOUNTS order by TNAME

DROP TABLE #ROWCOUNTS

DB一覧 †

select * from [master].[sys].[databases]