The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 2,387 other followers

MSSQL – searching in DDL metadata comments

Posted by jpluimers on 2021/04/14

Some interesting queries at [WayBack] www.nlaak.com • Просмотр темы – Полезные скрипты (English translation by Google)

I have put them in the [WayBack] gist below, and expect that they should be solvable without depending on deprecated sys.sysobjects.

Related: [WayBack] sql server – Making sense of sys.objects, sys.system_objects, and sys.sysobjects? – Database Administrators Stack Exchange

–jeroen

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))

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

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

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

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

view raw
view-locks.sql
hosted with ❤ by GitHub

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: