不用游标遍历数据库执行脚本

原贴: https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

--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

 

查询数据库某库下所有表的所有字段

SELECT TOP 100 a.TABLE_NAME,a.COLUMN_NAME,a.DATA_TYPE,a.CHARACTER_MAXIMUM_LENGTH,b.value 
from information_schema.COLUMNS as a  left join sys.extended_properties as b 
on a.TABLE_NAME=OBJECT_NAME(b.major_id) and a.ORDINAL_POSITION=b.minor_id 
where a.COLUMN_NAME LIKE '%parcelList%'