as title
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 |聚沙成塔,裡面包含文章中的案例實作,
期待能帶給您新的啟發或靈感。
未來文章將喬遷新址「一趟數據分析之旅」,歡迎追蹤繼續支持,您將不會錯過任何新知識。