Tag Archives: syscomments

Search objects like Stored Procedure, View, Table in SQL Server

SearchSearch objects like Stored Procedure, View, Table in SQL Server

It happens many time that we don’t know the exact name of stored procedure or table or view etc. and we have to either look at object explorer and scroll all the way down for object we are looking for or we can use “Filter” facility of SSMS but I prefer one small function I have written very long back because I am a script buddy and try to avoid mouse as long as possible.

Sometime, we may want to find out whether function, table or view we are looking for, is used in any other SP or View to check dependency, I used the same function. BTW, you can use “SP_Depends” or “Information_Schema.routines” to find dependency of the object which is more accurate way for find dependency.

Here is the table valued user defined function which I have developed a long back and still using it. In fact, I am so used to it now.


CREATE FUNCTION ObjectSearch

(

@SearchString VARCHAR(100)

)

RETURNS TABLE

RETURN

SELECT

DISTINCT scm.name AS SchemaName,

obj.name AS ObjectName,

obj.type_desc AS ObjectType

FROM sys.objects obj

INNER JOIN syscomments sysCom ON sysCom.Id = obj.object_id

INNER JOIN sys.schemas scm ON scm.schema_id = obj.schema_id

WHERE

is_ms_shipped = 0 AND sysCom.text like '%' + @SearchString + '%'

GO

Okay, not we have “ObjectSearch” function in our database. I have created this function in my AdventureWorks2012 database and now I am going to find where “Department” table is used so I would execute following simple SELECT statement with “ObjectSearch” function.


SELECT * FROM ObjectSearch('Department')

GO

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Find Specific word or phrase from all stored procedure, views, and triggers available in SQL Server 2005/2008

We may require sometime to find specific TableName or some words or some phrase have been used in any SP, Triggers or in VIEW. This is interesting to know how we can do it with T-SQL rather than manually checking script of everything.

Before I move forward to my original script, let me give you some basic logic behind that. Actually SYSObjects contains Name, ID along with many useful information of objects like stored procedure, view, trigger, function, user table, system table etc and SysComments contain ID of objects along with Text which has been used to create that object. So, now it may have popped up in your mind that I must have joined these two system views.

Before I show you the script I also would like to introduce you with the possible values could be used in “XType” column of “SysObjects” as that is the base and you can decide what to look (SP, Triggers, Views or anything else)

Here are some of the values of “Xtype” column and its meaning.

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

So now you are all set to look at the script, have a look at it.

–display the list of any trigger, view and stored procedure which has used ‘SY’ in the script

SELECT DISTINCT so.name,sc.text

FROM syscomments sc

INNER JOIN sysobjects so ON sc.id=so.id

WHERE so.xtype in (‘P’,‘TR’,‘V’) and sc.TEXT LIKE ‘%sy%’

order by name

 

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah