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

** テーブル行数一覧 [#t7ee2b8b]
 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
 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一覧 [#h6a0cdb5]
 select * from [master].[sys].[databases]
トップ   編集 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS