Полезные скрипты

Ответить
Kogep
Сообщения: 11
Зарегистрирован: 20 июн 2013, 12:32

Полезные скрипты

Сообщение Kogep »

1. Поиск по БД
Поиск в ХП

Код: Выделить всё

DECLARE @SubStr VARCHAR(8000)
SET @SubStr = '' -- нужная фраза в кавычках

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


Поиск в ХП/триггерах

Код: Выделить всё

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

SELECT @SEARCHSTRING = 'Text I am searching', @notcontain = ''

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

List of sysobjects.type

Код: Выделить всё

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

List of sysobjects.xtypes

Код: Выделить всё

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


2. Просмотр блокировок

Код: Выделить всё

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


2. Индексы (вместе с фильтрами)

Код: Выделить всё

SELECT * 
FROM sys.indexes AS i 
INNER JOIN sys.data_spaces AS ds ON i.data_space_id = ds.data_space_id 
WHERE is_hypothetical = 0 AND i.index_id <> 0   
Ответить