SQLSERVER 查询服务器中存储过程、视图、触发器、函数引用的外部对象

最新推荐文章于 2024-09-05 09:19:36 发布

原创最新推荐文章于 2024-09-05 09:19:36 发布 · 490 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#服务器 #sqlserver #数据库

本文档展示了如何使用SQL查询创建临时表#SRV和#data_list，以收集跨服务器的数据库对象信息，包括存储过程、函数、触发器和对象名，以及它们所在的链接服务器和数据库实例。

print('创建#server临时表')
IF OBJECT_ID('tempdb..#SRV') IS NOT NULL
    DROP TABLE #SRV;
select SRV.[name] ,SRV.[data_source] into #SRV 
from (
	select  LTRIM(RTRIM(SRV.[name]))+'.' name ,LTRIM(RTRIM(SRV.[data_source]))+'.' data_source 
	FROM sys.servers SRV
	union
	select  '['+LTRIM(RTRIM(SRV.[name]))+'].' [name],'['+LTRIM(RTRIM(SRV.[data_source]))+'].' [data_source] 
	FROM sys.servers SRV
	) SRV

print('创建#data_list临时表')
IF OBJECT_ID('tempdb..#data_list') IS NOT NULL
    DROP TABLE #data_list;

select ObjectType,ObjectName,[database],LinkedServerDataSource,[object] into #data_list
from(
SELECT distinct 
	SRV.[data_source] AS LinkedServerDataSource
	,PRO.[name] AS ObjectName
	,'存储过程' AS ObjectType
-------------------------------------------⬇链接服务器名称------------------------------------------------------
	,substring(
		OBJECT_DEFINITION(PRO.[object_id]),--存储过程文本
		charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])),--链接服务器长度起点
		len(SRV.[name])-1 ) [database]--链接服务器长度
-------------------------------------------⬇链接服务器、实例、引用表------------------------------------------------------
	,substring(
		OBJECT_DEFINITION(PRO.[object_id]),--存储过程文本
		charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])),--链接服务器长度终点
		charindex('.', OBJECT_DEFINITION(PRO.[object_id]), charindex(SRV.[name], OBJECT_DEFINITION(PRO.[object_id])) + len(SRV.[name]) ) 
		-(charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])))
			  ) [object]
FROM #SRV SRV,sys.procedures PRO
where  charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])) > 0

UNION

SELECT distinct  
	SRV.[data_source] AS LinkedServerDataSource
	,PRO.[name] AS ObjectName
	, '函数' AS ObjectType
-------------------------------------------⬇链接服务器名称------------------------------------------------------
	,substring(
		OBJECT_DEFINITION(PRO.[object_id]),--存储过程文本
		charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])),--链接服务器长度起点
		len(SRV.[name])-1 ) [database]--链接服务器长度
-------------------------------------------⬇链接服务器、实例、引用表------------------------------------------------------
	,substring(
		OBJECT_DEFINITION(PRO.[object_id]),--存储过程文本
		charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])),--链接服务器长度终点
		charindex('.', OBJECT_DEFINITION(PRO.[object_id]), charindex(SRV.[name], OBJECT_DEFINITION(PRO.[object_id])) + len(SRV.[name])) 
		-(charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])))
			  ) [object]
FROM #SRV SRV,sys.views PRO
where charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])) > 0

UNION

SELECT distinct 
	SRV.[data_source] AS LinkedServerDataSource
	,PRO.[name] AS ObjectName
	, '触发器' AS ObjectType
-------------------------------------------⬇链接服务器名称------------------------------------------------------
	,substring(
		OBJECT_DEFINITION(PRO.[object_id]),--存储过程文本
		charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])),--链接服务器长度起点
		len(SRV.[name])-1 ) [database]--链接服务器长度
-------------------------------------------⬇链接服务器、实例、引用表------------------------------------------------------
	,substring(
		OBJECT_DEFINITION(PRO.[object_id]),--存储过程文本
		charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])),--链接服务器长度终点
		charindex('.', OBJECT_DEFINITION(PRO.[object_id]), charindex(SRV.[name], OBJECT_DEFINITION(PRO.[object_id])) + len(SRV.[name])) 
		-(charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])))
			  ) [object]
FROM #SRV SRV,sys.triggers PRO
where charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])) > 0

UNION

SELECT distinct 
	SRV.[data_source] AS LinkedServerDataSource
	,PRO.[name] AS ObjectName
	, '函数' AS ObjectType
-------------------------------------------⬇链接服务器名称------------------------------------------------------
	,substring(
		OBJECT_DEFINITION(PRO.[object_id]),--存储过程文本
		charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])),--链接服务器长度起点
		len(SRV.[name])-1 ) [database]--链接服务器长度
-------------------------------------------⬇链接服务器、实例、引用表------------------------------------------------------
	,substring(
		OBJECT_DEFINITION(PRO.[object_id]),--存储过程文本
		charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])),--链接服务器长度终点
		charindex('.', OBJECT_DEFINITION(PRO.[object_id]), charindex(SRV.[name], OBJECT_DEFINITION(PRO.[object_id])) + len(SRV.[name])) 
		-(charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])))
			  ) [object]
FROM #SRV SRV,sys.objects PRO
where charindex(SRV.[name],OBJECT_DEFINITION(PRO.[object_id])) > 0
	and PRO.[type] in ('FN', 'IF', 'FN', 'AF', 'FS', 'FT')
)a
print('汇总查询')
----------------------------------------⬇汇总查询----------------------------------------------------
SELECT  ObjectType,ObjectName ,
	[object] = ( STUFF(( SELECT ',' + [object]
                          FROM #data_list
                          WHERE ObjectName = Test.ObjectName and ObjectType = Test.ObjectType
						FOR
                          XML PATH('')
                        ), 1, 1, '') ),
	[LinkedServerDataSource] = ( STUFF(( SELECT ',' + [database]+':'+substring([LinkedServerDataSource],1,len([LinkedServerDataSource])-1) 
                          FROM #data_list
                          WHERE ObjectName = Test.ObjectName and ObjectType = Test.ObjectType
						FOR
                          XML PATH('')
                        ), 1, 1, '') )	
FROM #data_list AS Test
GROUP BY ObjectType,ObjectName;