--创建函数
USE tempdb
GO
IF OBJECT_ID ('dbo.P_QueryTableRecord') IS NOT NULL
DROP PROCEDURE dbo.P_QueryTableRecord
GO
CREATE PROCEDURE P_QueryTableRecord(
@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 @@SQLSTATUS = 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' "
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 @@SQLSTATUS = 0
BEGIN
--4.2.5执行脚本
PRINT @querysql
EXECUTE (@querysql)
--4.2.6处理下一条
FETCH tbs INTO @tablename, @querysql
END
CLOSE tbs
DEALLOCATE CURSOR tbs
--4.3 处理下一条
FETCH dbs INTO @dbname
END
--5.关闭游标
CLOSE dbs
--6.销毁游标
DEALLOCATE CURSOR 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..P_QueryTableRecord 'DB_TASK'
GO