SQL Server - 利用關鍵字尋找SQL物件

在Sustain系統時,找不到特定SQL物件用在哪裡的時候,可以利用下列語法去找.

declare @keyword varchar(100)

set @keyword='%我要找的關鍵字%'

使用到@keyword的所有SQL元件

• SQL SERVER 2005

-- 欄位名稱有符合關鍵字模糊比對之table列表

SELECT * FROM sys.tables where object_id in (

SELECT object_id FROM sys.all_columns where name like @keyword

) order by name

-- 欄位名稱有符合關鍵字模糊比對之view列表

SELECT * FROM sys.views where obj ect_id in (

SELECT object_id FROM sys.all_columns where name like @keyword

) order by name

-- sql語法有符合關鍵字模糊比對之物件列表(StoreProcedure/Function ...)

select * from sys.all_objects  where object_id in (

select object_id from sys.all_sql_modules where definition like @keyword

)

• SQL SERVER 2000

-- 取得DB與@keyword有關聯性之object name/sql語法

select o.[name],c.[text] from syscomments c left join sysobjects o on o.id=c.id 

where c.id in (

select distinct id from sysdepends where depid in (

select id from sysobjects where [name] like @keyword

)

) 

-- 取得sql語法中具備@keyword關鍵字之所有物件

select o.[name],c.[text] from syscomments c left join sysobjects o on o.id=c.id

where c.[text]  like @keyword