This post describes the how can you find list of stored procedure with specific text content or how to answer following question.
- List All Stored Procedure, Function, Views, Triggers which uses a Update or Insert or Delete Statement.
- List All Stored Procedure, Function, Views, Triggers which uses temporary table.
- List All Stored Procedure, Function, Views, Triggers which uses a particular table.
- and Many More.
There is a system table which contains information about stored procedure,view and trigger contents “syscomments”
List All Stored Procedure, Functions, Views, Triggers which uses “Update, Insert or Delete Statement”
Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text Like '%update %' or text like '%insert %' or text like '%delete %'
List All Stored Procedure, Functions, Views, Triggers which uses temporary table.
Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where Replace(Text,' ','') Like '%Table#%'
List All Stored Procedure, Functions, Views, Triggers which uses a particular table.
Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text like '%Sys.Objects%'
List All Stored Procedure , Functions, Views, Triggers which contains a specific text
Select Distinct A.Name,A.type_desc
from SysComments B
Join sys.all_objects A On B.id=A.object_id
where text like '%Data%'
So “syscomments” is really helpful.
NOTE: Searching cannot be done when stored procedure function or View Are Encrypted.
create procedure dbo.proc_SearchAnyThingInModule(@searchVal varchar(200))
ReplyDeleteas
SELECT
o.type_desc AS ROUTINE_TYPE,
o.[name] AS ROUTINE_NAME,
m.definition AS ROUTINE_DEFINITION
FROM
sys.sql_modules AS m
INNER JOIN sys.objects AS o ON m.object_id = o.object_id
WHERE
m.definition LIKE '%'+@searchVal+'%'
go
--proc_SearchAnyThingInModule '%any thing%'
https://saglamproxy.com
ReplyDeletemetin2 proxy
proxy satın al
knight online proxy
mobil proxy satın al
6YFC