--This query will return a listing of all tables in all databases on a SQL instance: DECLARE @command varchar(1000) SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' ORDER BY name' EXEC sp_MSforeachdb @command
--This statement creates a stored procedure in each user database that will return a listing of all users in a database, sorted by their modification date DECLARE @command varchar(1000) SELECT @command = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'') BEGIN USE ? EXEC(''CREATE PROCEDURE pNewProcedure1 AS SELECT name, createdate, updatedate FROM sys.sysusers ORDER BY updatedate DESC'') END' EXEC sp_MSforeachdb @command
--This query will return a listing of all files in all databases on a SQL instance: EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'
查询跨库存储过程调用,没有直接的方法:
DECLARE @sql NVARCHAR(2000)='USE ?
SELECT DB_NAME(); SELECT DISTINCT so.name FROM syscomments sc INNER JOIN sysobjects so ON sc.id=so.id WHERE sc.TEXT LIKE ''%P_ThisIsAStoredProcedure%''' EXEC sp_MSforeachdb @sql
联合sp_MSforeachdb,SP_MSFOREACHTABLE,sp_spaceused 查询各库各表的大小:
DECLARE @sql NVARCHAR(2000)='USE ^; --SELECT DB_NAME(); EXEC sp_spaceused; --EXEC SP_MSFOREACHTABLE ''EXEC sp_spaceused "?"'' CREATE TABLE #t ( name VARCHAR(255), ROWS BIGINT, reserved VARCHAR(20), DATA VARCHAR(20), index_size VARCHAR(20), unused VARCHAR(20) ) EXEC sp_MSforeachtable "insert into #t exec sp_spaceused ''?''" SELECT *,CAST(SUBSTRING(data,0,LEN(data)-2) AS float)/1024 as Data_MB FROM #t ORDER BY Data_MB desc DROP TABLE #t ' EXEC sp_MSforeachdb @sql,'^'
查所有数据库表大小:
create table #temp1 ([数据库名] varchar(50), [数据库大小(MB)] dec (15,2), [未分配空间(MB)] dec (15,2), [保留(MB)] dec (15,2), [数据(MB)] dec (15,2), [索引(MB)] dec (15,2), [未使用(MB)] dec (15,2)) insert into #temp1 exec sp_msforeachdb 'use ?; select db_name(), convert(dec (15,2),(convert(dec (15,2),dbsize) + convert (dec (15,2),logsize)) * 8192 / 1048576), convert(dec (15,2),(case when dbsize >= reservedpages then (convert (dec (15,2),dbsize) - convert (dec (15,2),reservedpages)) * 8192 / 1048576 else 0 end)), convert(dec (15,2),reservedpages * 8192 / 1048576.), convert(dec (15,2),pages * 8192 / 1048576.), convert(dec (15,2),(usedpages - pages) * 8192 / 1048576.), convert(dec (15,2),(reservedpages - usedpages) * 8192 / 1048576.) FROM ( SELECT sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) AS dbsize , sum(convert(bigint,case when status & 64 <> 0 then size else 0 end)) AS logsize from dbo.[sysfiles] ) a LEFT JOIN ( select sum(a.total_pages) AS reservedpages, SUM(a.used_pages) AS usedpages, SUM( CASE -- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size" When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0 When a.type <> 1 Then a.used_pages When p.index_id < 2 Then a.data_pages Else 0 END ) AS pages from [sys].[partitions] p join [sys].[allocation_units] a on p.partition_id = a.container_id left join [sys].[internal_tables] it on p.object_id = it.object_id ) b ON 1=1 ' select * from #temp1 ORDER BY [数据库大小(MB)] desc drop table #temp1