一、创建函数
USE tempdb
GO
IF OBJECT_ID ('dbo.PR_QUERY_TABLE_RECORD') IS NOT NULL
DROP PROCEDURE dbo.PR_COUNT_TABLE_RECORD
GO
CREATE PROCEDURE PR_COUNT_TABLE_RECORD
(
@dbname_prefix VARCHAR(300) --数据库前缀名
)
AS
BEGIN
DECLARE
@tmpSql VARCHAR(900),
@dbname VARCHAR(300),
@tablename VARCHAR(300),
@querysql VARCHAR(900)
CREATE TABLE #tmp_all_table_record(
dbname VARCHAR(300),
tablename VARCHAR(300),
rowcounts INT
)
CREATE TABLE #tmp_tbs(
tablename VARCHAR(300),
querysql VARCHAR(900)
)
--1.定义游标(所有符合前缀条件的库名)
DECLARE dbs CURSOR FOR SELECT name FROM master..sysdatabases WHERE name LIKE @dbname_prefix + '%'
--2.打开游标
OPEN dbs
--3.获取记录
FETCH dbs INTO @dbname
--4.循环处理每个库
WHILE @@FETCH_STATUS = 0
BEGIN
--4.1 清空临时表
TRUNCATE TABLE #tmp_tbs
--4.2处理临时表
SELECT @tmpSql = 'INSERT INTO #tmp_tbs SELECT name, ''INSERT INTO #tmp_all_table_record '
+ ' SELECT ''''' + @dbname + ''''', '''''' + name + '''''', COUNT(*) FROM ' + @dbname + '..'' + name FROM ' + @dbname + '..sysobjects WHERE type = ''U'' '
print @tmpSql
EXECUTE (@tmpSql)
--4.2.1 定义表列表游标
DECLARE tbs CURSOR FOR SELECT tablename, querysql FROM #tmp_tbs
--4.2.2 打开表列表游标
OPEN tbs
--4.2.3 获取记录
FETCH tbs INTO @tablename, @querysql
--4.2.4处理每个表
WHILE @@FETCH_STATUS = 0
BEGIN
--4.2.5执行脚本
PRINT @querysql
EXECUTE (@querysql)
--4.2.6处理下一条
FETCH tbs INTO @tablename, @querysql
END
CLOSE tbs
DEALLOCATE tbs
--4.3 处理下一条
FETCH dbs INTO @dbname
END
--5.关闭游标
CLOSE dbs
--6.销毁游标
DEALLOCATE dbs
SELECT dbname, tablename, rowcounts FROM #tmp_all_table_record ORDER BY rowcounts DESC
DROP TABLE #tmp_tbs
DROP TABLE #tmp_all_table_record
END
GO
二、执行函数
USE tempdb
GO
EXECUTE tempdb..PR_COUNT_TABLE_RECORD 'DB_TASK'
GO
三、查看结果
执行完毕后返回的结果集就是我们想要的结果。在需要时也可以修改存储过程中的临时表为实体表,根据需求调整即可