SQL Server 查資料庫中所有表的筆數

查資料庫中所有表的筆數 所以USE

  USE [EOICARD_old]                       --change DB
  SELECT TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,
  ROW_NUMBER() OVER (ORDER BY TABLE_SCHEMA,TABLE_NAME) [no]  
  into #tmp 
  FROM INFORMATION_SCHEMA.TABLES;                   --create temp table

  DECLARE @tb_name nvarchar(100), @num int, @total int, @i int,            --declare variable
    @tb_schema nvarchar(100), @sql nvarchar(100); 
  DECLARE @tble_total table (                    --declare output table
 schema_ nvarchar(100),
 name nvarchar(100),
 rowtotal int
  );  
                       
  SELECT @num=count(*) FROM #tmp;                   --set total table count
  SET @i=1;                         --set loop count

  while @i<=@num                       --loop start
  BEGIN
 SELECT @tb_schema=TABLE_SCHEMA FROM #tmp;                --get schema name
 SET @tb_name=(SELECT TABLE_NAME FROM #tmp WHERE [no]=@i);            --get table name
 SET @sql=N'SELECT '''+@tb_schema+''','''+@tb_name+''',count(*) FROM '+ @tb_schema+'.'+@tb_name;   --set sql statement
 INSERT INTO @tble_total(schema_,name,rowtotal) EXEC(@sql)            --temp table row count
 SET @total = (select top (1) rowtotal from @tble_total WHERE name=@tb_name);       --set row count
 SET @i=@i+1;                       --set next loop
  END                          --loop end

SELECT * FROM @tble_total;                     --output
DROP TABLE #tmp;                       --drop tmp table
 GO

結果會大致長這樣，有schema、table name、總筆數