聚沙成塔

ALTER PROCEDURE [dbo].[SP_WhereSP]
(
	@keyword nvarchar(300) 
)
AS 
SET NOCOUNT ON 
BEGIN
-------------------------------------------------
--變數定義
IF OBJECT_ID('tempdb..#WhereSPDBList') IS NOT NULL
	DROP TABLE #WhereSPDBList
		
select name
into #WhereSPDBList  
FROM sys.databases with(nolock)
--where [name]='Services'

Declare @DBName nvarchar(25)
Declare @sql nvarchar(MAX)
Declare @keyword_in nvarchar(300)

set @keyword_in= @keyword ;
-------------------------------------------------
--建立查詢結果彙總資料表
IF OBJECT_ID('tempdb..##WhereSPResult') IS NOT NULL
	drop table ##WhereSPResult;
create table ##WhereSPResult
(
	ObjectType nvarchar(10),
	DBName nvarchar(100),
	ObjectName nvarchar(200),
	Created datetime,
	Last_altered datetime
);
-------------------------------------------------
--分資料庫查詢相關物件
Declare MyCursor Cursor FOR
Select [name] from #WhereSPDBList  --for迴圈裡面的資料
Open MyCursor 
	Fetch NEXT FROM MyCursor INTO @DBName
	While (@@FETCH_STATUS <> -1)
	BEGIN
		--確保每一次使用都沒有資料表
		IF OBJECT_ID('tempdb..##temp_WhereSP_result') IS NOT NULL
			drop table ##temp_WhereSP_result
		
		--step1: 指定DB查詢相關物件
		set @sql='' ;
		set @sql= 'use '+ @DBName + ';';
		set @sql= @sql +  'select obj.type as ObjectType, '''+@DBName+''' as DBName, obj.Name as ObjectName,obj.crdate, obj.refdate  into ##temp_WhereSP_result FROM sys.sysobjects as obj INNER JOIN syscomments as cm ON obj.id = cm.id WHERE cm.text LIKE ''%'+ @keyword_in + '%'';' ;
		--print @sql --debug
		BEGIN TRY
			EXECUTE sp_executesql @sql;
		END TRY
		BEGIN CATCH
			--print 'error' ; --跳出此次loop
		END CATCH 
		--step2: 將資料insert 整理到資料表
		IF OBJECT_ID('tempdb..##temp_WhereSP_result') IS NOT NULL
			insert into ##WhereSPResult
			select 
				case  
					when ObjectType='P' then 'Procedure'
					when ObjectType='V' then 'View'
					else ObjectType
				end as ObjectType,
				DBName,
				ObjectName,
				crdate as Created,
				refdate as Last_altered  
			from ##temp_WhereSP_result;
		IF OBJECT_ID('tempdb..##temp_WhereSP_result') IS NOT NULL
			drop table ##temp_WhereSP_result
		Fetch NEXT FROM MyCursor INTO @DBName
	END

--關閉&釋放cursor
CLOSE MyCursor
DEALLOCATE MyCursor
-------------------------------------------------
select 
	ObjectType,
	DBName,
	ObjectName,
	Created,
	Last_altered

from ##WhereSPResult
where 1=1
	and DBName <> 'master'
order by 1 asc, Created desc;
drop table ##WhereSPResult;

END
GO

 

同時也歡迎追蹤Tableau Public Gallery- MR.360 |聚沙成塔，裡面包含文章中的案例實作，
期待能帶給您新的啟發或靈感。

未來文章將喬遷新址「一趟數據分析之旅」，歡迎追蹤繼續支持，您將不會錯過任何新知識。