MSSQL - searching in DDL metadata comments; from http://www.nlaak.ru/viewtopic.php?f=37&t=136

Search-in-comments-of-StoredProcedure-Function-Trigger-View-Function.sql

DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)

SELECT @SEARCHSTRING = 'Text I am searching', @notcontain = 'text that should be excluded'

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
when sysobjects.xtype = 'V' then 'View'
when sysobjects.xtype = 'FN' then 'Function'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR','V','FN')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
AND ((CHARINDEX(@notcontain,syscomments.text)=0
or CHARINDEX(@notcontain,syscomments.text)<>0))

Search-in-comments-of-StoredProcedure.sql

DECLARE @SubStr VARCHAR(8000)
SET @SubStr = '' -- quoted serch term (used as substring)

SELECT
 o.name,
 c.text
FROM
 [sys].[objects] AS o

 INNER JOIN syscomments AS c
 ON o.object_id = c.id

WHERE
 o.[TYPE]='P'
 AND c.text LIKE '%' + @SubStr + '%'
ORDER BY o.name

sys.sysobjects.type.values.txt

C = CHECK constraint 
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint 
K = PRIMARY KEY or UNIQUE constraint 
L = Log
P = Stored procedure
R = Rule
RF = Replication filter stored procedure
S = System table 
TR = Trigger
U = User table
V = View
X = Extended stored procedure

sys.sysobjects.xtype.values.txt

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure 
S = System table
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

view-locks.sql

SELECT 
so.Name, 
convert (smallint, req_spid) As spid, 
rsc_dbid As dbid, 
rsc_objid As ObjId, 
so.name as ObjName, 
rsc_indid As IndId, 
substring (v.name, 1, 4) As Type, 
substring (rsc_text, 1, 16) as Resource, 
substring (u.name, 1, 8) As Mode, 
substring (x.name, 1, 5) As Status 
FROM master.dbo.syslockinfo sl 
INNER JOIN master.dbo.spt_values v ON sl.rsc_type = v.number 
INNER JOIN master.dbo.spt_values x ON sl.req_status = x.number 
INNER JOIN master.dbo.spt_values u ON sl.req_mode + 1 = u.number 
LEFT JOIN sysobjects so ON sl.rsc_objid = so.ID 
WHERE v.type = 'LR' and x.type = 'LS' and u.type = 'L' 
ORDER BY spid, ObjName
Kogep
Messages: 8
Registered: Jun 20 2013, 12:32
to come back to the beginning