笔记185 系统存储过程sp_MSforeachtable和sp_MSforeachdb，用于遍历某个数据库的每个表和遍历DBMS管理下的每个数据库(查询数据库所有表的记录总数)

笔记185 系统存储过程sp_MSforeachtable和sp_MSforeachdb，用于遍历某个数据库的每个表和遍历DBMS管理下的每个数据库(查询数据库所有表的记录总数)

 1 --系统存储过程sp_MSforeachtable和sp_MSforeachdb，用于遍历某个数据库的每个表和遍历DBMS管理下的每个数据库(查询数据库所有表的记录总数)
 2 EXEC sys.sp_MSforeachtable  @command1 = N'', -- nvarchar(2000)
 3     @replacechar = N'', -- nchar(1)
 4     @command2 = N'', -- nvarchar(2000)
 5     @command3 = N'', -- nvarchar(2000)
 6     @whereand = N'', -- nvarchar(2000)
 7     @precommand = N'', -- nvarchar(2000)
 8     @postcommand = N'' -- nvarchar(2000)
 9 
10 EXEC sys.sp_MSforeachdb  @command1 = N'', -- nvarchar(2000)
11     @replacechar = N'', -- nchar(1)
12     @command2 = N'', -- nvarchar(2000)
13     @command3 = N'', -- nvarchar(2000)
14     @precommand = N'', -- nvarchar(2000)
15     @postcommand = N'' -- nvarchar(2000)
16 
17 --查询数据库所有表的记录总数
18 USE GPOSDB
19 CREATE TABLE #temp(tablename VARCHAR(255),rowcnt INT)
20 EXEC sys.sp_MSforeachtable  'insert into #temp select "?" ,count(*) from ?' --加双引号的解释见下面
21 SELECT tablename,rowcnt FROM #temp ORDER BY tablename
22 DROP TABLE #temp
23 
24 --sql查询所有用户表的列表，详细信息，如：记录数，表占用大小等
25 EXEC   sp_MSforeachtable   'EXECUTE   sp_spaceused   "?"'   --因为sp_spaceused的objname参数是字符串所以问号要加双引号不知道为什麽不能加单引号 @objname = N''
26 
27 --EXEC sys.sp_spaceused  @objname = N'', -- nvarchar(776)
28 --    @updateusage = '' -- varchar(5)

 

https://raresql.com/2014/02/11/sql-server-how-to-filter-databases-in-sp_msforeachdb/

sp_MSforeachdb过滤某些数据库

EXEC sp_MSforeachdb
@command1='IF ''?''
IN (''AdventureWorks2012'',''AdventureWorks2012_test'')
BEGIN
       SELECT name,object_id,modify_date
       FROM ?.sys.tables WHERE [name]=''Person''
END'

 

 

实例下各个数据库数据分析2

EXEC sys.[sp_MSforeachdb] @command1 = N'IF ''?'' NOT IN (''master'',''tempdb'',''model'',''msdb'',''ReportServer'',''ReportServerTempDB'',''distribution'')
BEGIN
     USE ?
IF OBJECT_ID(''tempdb..#TablesSizes'') IS NOT NULL
    DROP TABLE #TablesSizes

CREATE TABLE #TablesSizes
    (
      TableName sysname ,
      Rows BIGINT ,
      reserved VARCHAR(100) ,
      data VARCHAR(100) ,
      index_size VARCHAR(100) ,
      unused VARCHAR(100)
    )

DECLARE @sql NVARCHAR(MAX)
SELECT  @sql = COALESCE(@sql, '''') + ''INSERT INTO #TablesSizes execute sp_spaceused '''''' + QUOTENAME(TABLE_SCHEMA,''[]'') + ''.''+ QUOTENAME(Table_Name, ''[]'') +''''''''
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_TYPE = ''BASE TABLE''
EXECUTE (@SQL)
alter table #TablesSizes add [DBNAME] VARCHAR(100) null
update #TablesSizes set [DBNAME] = ''?''
SELECT  *
FROM    #TablesSizes
ORDER BY Rows DESC
       
END'    -- nvarchar(2000)

 

 

posted @ 2013-08-04 12:58  桦仔  阅读(453)  评论(0)  编辑  收藏  举报