| Search text in Triggers and Store Prodedures |
|
|
| Written by Stanislav Duben | |||||||||
| Friday, 24 August 2007 | |||||||||
 If you want search text not only in name of triggers and store procedures, using search object in query analyzer is not enough and in MS SQL 2005 there isn't any search object function. So you need other way how to do it.There is code for searching in xtype objects, that contains body of triggers, store procedures and other objects inside MS SQL database. This example show you how to search in triggers and store procedures. DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)Into @SEARCHSTRING you can assign any text you need search. If you want exclude some results with specify text, you can use @notcontain variable. You can use also other objects for searching. Then you need change case condition, and add other xtypes you want to use. Then change where condition, add sysobjects.type in association to used xtypes. List of possible objects of sysobjects.type and sysobjects.xtype is bellow and is very similar. There is only one different, type K (primary key or UNIQUE constraint) is in xtype splitted to PK (primary key) and UQ (UNIQUE contraint). 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  Quote this article on your site | Views: 2540 | Print
Powered by AkoComment Tweaked Special Edition v.1.4.6 Tag it:     |
|||||||||
Comments (1)
| < Prev | Next > |
|---|