Dunk

--原始的SQL(需切換資料庫)
SELECT so.name AS [資料表名稱]
	 , max(si.rows) AS [筆數]

FROM
	sysobjects AS so
	INNER JOIN sysindexes AS si
		ON object_id(so.name) = si.id

WHERE
	so.xtype = 'U'

GROUP BY
	so.name
ORDER BY
	[筆數] DESC
	
-- 尋找所有線上運作的資料庫名稱
USE master

SELECT 0 AS rkno
	 , N'請選擇資料庫' AS name
UNION
SELECT row_number() OVER (ORDER BY name) AS rkno
	 , name
FROM
	sys.databases
WHERE
	state <> 6
	AND name NOT IN ('master', 'model', 'msdb', 'tempdb')


-- 查詢單一資料庫表格總筆數
DECLARE @dbname varchar(50)
DECLARE @SQLString nvarchar(3000)
SET @dbname = 'chb_eform';
SET @SQLString = '
use [' + @dbname + '];

SELECT ''' + @dbname + ''' as [資料庫名稱],so.name AS [資料表名稱]
	 , max(si.rows) AS [筆數]

FROM
	sysobjects AS so
	INNER JOIN sysindexes AS si
		ON object_id(so.name) = si.id

WHERE
	so.xtype = ''U''

GROUP BY
	so.name
ORDER BY
	[筆數] DESC';
EXEC sp_executesql @SQLString	

-- 掃所有的資料庫表格總筆數

USE master
GO

CREATE Table ##CountTotalNum ( [資料庫名稱] sysname, [資料表名稱] sysname, [筆數] sysname)
DECLARE  @tableNameList TABLE 
(
	counted int,
	tableName varchar(50)   		
)

INSERT INTO @tableNameList
SELECT row_number() OVER (ORDER BY name) AS rkno
	 , name
FROM
	sys.databases
WHERE
	state <> 6
	AND name NOT IN ('master', 'model', 'msdb', 'tempdb')
DECLARE @count int
SELECT @count = count(1)
FROM
	@tableNameList;

DECLARE  @tablename nvarchar(200)
DECLARE @dbname varchar(50)
DECLARE @SQLString nvarchar(3000)

WHILE @count > 0
BEGIN
SELECT @tablename = tableName
FROM
	@tableNameList
WHERE
	counted = @count

SET @dbname = @tablename;
SET @SQLString = '
use [' + @dbname + '];
insert into ##CountTotalNum
SELECT ''' + @dbname + ''' as [資料庫名稱],so.name AS [資料表名稱]
	 , max(si.rows) AS [筆數]

FROM
	sysobjects AS so
	INNER JOIN sysindexes AS si
		ON object_id(so.name) = si.id

WHERE
	so.xtype = ''U''

GROUP BY
	so.name
ORDER BY
	[筆數] DESC';
EXEC sp_executesql @SQLString
SELECT @count = @count - 1
End

SELECT *
FROM
	##CountTotalNum
ORDER BY
	[資料庫名稱]
DROP TABLE ##CountTotalNum