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' 的效果

声明: 本文版权归作者dotNetDR_和博客园共有，转载必须保留此段声明。