查資料庫中所有表的筆數
所以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、總筆數