Transact-SQL 示例 - 查询某个数据库内的所有表的记录行数及其总和

直接上SQL脚本

CREATE PROCEDURE dbo.ShowAllTableRows@databaseName NVARCHAR(100) AS BEGIN--declare @databaseName nvarchar(100)--set @databaseName = 'tempdb'DECLARE @dynamicSql NVARCHAR(4000)SET @dynamicSql = N'USE ' + @databaseName + N';SET NOCOUNT ON;DECLARE cur CURSOR LOCAL FORWARD_ONLY READ_ONLYFOR SELECT ''['' + s.name + '']'' + ''.['' + t.name + '']'' TableNameFROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id;OPEN cur--IF EXISTS (SELECT object_id(N''tempdb..#table''))-- DROP TABLE #tableCREATE TABLE #table ([TableName] NVARCHAR(300) PRIMARY KEY,[Rows] INT)DECLARE @tableName VARCHAR(100)FETCH NEXT FROM cur INTO @tableNameWHILE @@FETCH_STATUS = 0BEGINDECLARE @sql NVARCHAR(2000)SET @sql = ''INSERT INTO #table ([TableName], [Rows]) VALUES (''SET @sql = @sql + '''''''' + @tableName + '''''', '' + ''(SELECT COUNT(*) FROM '' + @tableName + ''))''exec sp_executesql @sql--print @sqlFETCH NEXT FROM cur INTO @tableNameENDCLOSE curDEALLOCATE curSELECT * FROM #table ORDER BY [Rows] DESCDECLARE @allRows INT;SET @allRows = (SELECT SUM([Rows]) FROM #table);PRINT @allRows;';--PRINT @dynamicSql;exec sp_executesql @dynamicSql END

执行: dbo.ShowAllTableRows 'AdventureWorks' 的效果

转载于:https://www.cnblogs.com/highend/archive/2011/07/25/transact_sql_examples_for_show_all_tabls_row_count.html

总结

以上是生活随笔为你收集整理的Transact-SQL 示例 - 查询某个数据库内的所有表的记录行数及其总和的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。