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_ONLY
   FOR 
      SELECT 
         ''['' + s.name + '']'' 
         + ''.['' + t.name + '']'' TableName
      FROM 
         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 #table

   CREATE TABLE #table (
      [TableName] NVARCHAR(300) PRIMARY KEY,
      [Rows] INT
   )

   DECLARE @tableName VARCHAR(100)

   FETCH NEXT FROM cur 
   INTO @tableName

   WHILE @@FETCH_STATUS = 0
   BEGIN
      DECLARE @sql NVARCHAR(2000)
      SET @sql = ''INSERT INTO #table ([TableName], [Rows]) VALUES (''
      SET @sql = @sql + '''''''' + @tableName + '''''', '' 
         + ''(SELECT COUNT(*) FROM '' + @tableName + ''))''
         
      exec sp_executesql @sql
      --print @sql
            
      FETCH NEXT FROM cur 
      INTO @tableName
   END

   CLOSE cur
   DEALLOCATE cur

   SELECT * FROM #table ORDER BY [Rows] DESC
   DECLARE @allRows INT;
   SET @allRows = (SELECT SUM([Rows]) FROM #table);
   PRINT @allRows;';
   
   --PRINT @dynamicSql;
   exec sp_executesql @dynamicSql
END

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