【SQL Sever】全ユーザテーブルのレコード数取得

これもあまり使うことないと思うけど、移行時の見積もりとかに使えるかも・・・。

use tempdb

declare @tbl varchar(100),@sSQL nvarchar(1000),@parm nvarchar(100),@cnt int

declare cu cursor for
SELECT
 s.name
FROM
 sys.objects s
WHERE
 s.type IN (N'U')

open cu

fetch next from cu into @tbl
while @@fetch_status = 0
begin
 set @sSQL=null
 set @sSQL='select @c = count(*) from ' + @tbl
   set @parm='@c int OUTPUT'
 Execute sp_executesql @sSQL ,@parm,@c=@cnt OUTPUT
 print @tbl + '=' + convert(varchar,@cnt) fetch next from cu into @tbl end

close cu
deallocate cu