Find for SQL Objects, Entities or Text

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sunil Chandurkar » Find for SQL Objects, Entities or Text

Find for SQL Objects, Entities or Text

Rate Topic

Display Mode

Topic Options

Author

Message

Sunil Chandurkar

Posted Wednesday, May 28, 2008 2:02 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, September 27, 2012 4:24 AM
Points: 253, Visits: 78

Comments posted to this topic are about the item Find for SQL Objects, Entities or Text

Post #508001

Rudy Panigas

Posted Thursday, July 03, 2008 9:13 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 306, Visits: 1,014

Hello,

Great idea for the stored procedure. For some reason the code does not execute. Below are the errors I'm getting. Is there an updated version??

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 55
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 56
Incorrect syntax near ' '.
Msg 137, Level 15, State 2, Line 61
Must declare the scalar variable "@ObjectType".
Msg 137, Level 15, State 2, Line 63
Must declare the scalar variable "@ObjectType".
Msg 137, Level 15, State 2, Line 69
Must declare the scalar variable "@SearchText".
Msg 137, Level 15, State 2, Line 70
Must declare the scalar variable "@SearchText".
Msg 137, Level 15, State 2, Line 75
Must declare the scalar variable "@SearchType".
Msg 137, Level 15, State 2, Line 78
Must declare the scalar variable "@SearchType".
Msg 102, Level 15, State 1, Line 80
Incorrect syntax near ' '.
Msg 137, Level 15, State 2, Line 90
Must declare the scalar variable "@SearchType".
Msg 102, Level 15, State 1, Line 92
Incorrect syntax near ' '.
Msg 137, Level 15, State 2, Line 101
Must declare the scalar variable "@SearchType".
Msg 102, Level 15, State 1, Line 103
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 114
Incorrect syntax near ' '.
Msg 137, Level 15, State 2, Line 114
Must declare the scalar variable "@OrderBy".
Msg 102, Level 15, State 1, Line 116
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 119
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Line 120
Incorrect syntax near 'END'.

Post #528168

Sunil Chandurkar

Posted Saturday, July 05, 2008 6:08 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, September 27, 2012 4:24 AM
Points: 253, Visits: 78

Hi,

Can you post the staments that your are trying to execute? I will be able to help you in better manner after viewing those sql statements.

Post #528826

Rudy Panigas

Posted Monday, July 07, 2008 6:46 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 306, Visits: 1,014

Hello,

I pasted your code (from this site). Looks like maybe your complete code was not uploaded to this site properly. Could you try to download your code from here and look at it?

Thanks

Post #529224

Sunil Chandurkar

Posted Sunday, July 27, 2008 10:49 PM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, September 27, 2012 4:24 AM
Points: 253, Visits: 78

Hi,

I have uploaded whole code on the site. I don't know why your are not able to run the code.

Can you post the code your are trying to run in your query editor window?

Post #541603

miguel1974

Posted Wednesday, January 14, 2009 11:49 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, April 10, 2013 12:13 PM
Points: 63, Visits: 224

Hello,

Thanks for the input you've provided.
However, I think you'd get better results using this function: object_definition(object_id)

Example:

SELECT TOP 1 object_id, object_definition(object_id) as Definition
FROM sys.all_objects
WHERE NOT(object_definition(object_id) IS NULL)

Keep up the good work.
Miguel

Post #636529

fraymond 69364

Posted Wednesday, March 31, 2010 8:25 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 31, 2010 8:24 AM
Points: 1, Visits: 0

I created some simpler queries in my blog http://geekatwork.wordpress.com/2010/03/30/database-schema-search/

Search for Text in Stored Procedures

DECLARE @SEARCH_TERM VARCHAR(100)
SET @SEARCH_TERM = ‘%BANKCARD_999%’

SELECT
ROUTINE_NAME,
ROUTINE_DEFINITION
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
ROUTINE_DEFINITION LIKE @SEARCH_TERM
ORDER BY
ROUTINE_NAME

Search for Text in All Columns

DECLARE @SEARCH_TERM VARCHAR(100)
SET @SEARCH_TERM = ‘%BANKCARD_999%’

SELECT
TABLE_NAME,
COLUMN_NAME
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME LIKE @SEARCH_TERM
ORDER BY
TABLE_NAME,
COLUMN_NAME

Search for Text in All Database Objects

DECLARE @SEARCH_TERM VARCHAR(100)
SET @SEARCH_TERM = ‘%BANKCARD_999%’

SELECT
o.NAME,
c.TEXT
FROM
sysobjects o
INNER JOIN
syscomments c
ON
o.id = c.id
WHERE
c.TEXT LIKE @SEARCH_TERM
o.TYPE IN (
‘P’, — Stored Procedure
‘V’, — View
‘FN’, — Function,
‘PK’ — Key
)

Post #893820

Jorge Serres

Posted Wednesday, April 18, 2012 3:07 PM

Forum Newbie

Group: General Forum Members
Last Login: Monday, September 17, 2012 10:44 PM
Points: 7, Visits: 118

Was testing it and it runs gr8! However, it'd be good if could be extended to run for multiple Databases within the given server or even servers.
So, I'd add two columns to the table. Servername and DatabaseName both of type sysname. The extend the code accordingly.

Sometimes I needed to identify in which Databases and servers does a given sproc or table exists.

So, I wrote about 5 years ago a sproc that would actually comb all sysservers (via linked) and collect all the info for every DB.
Today a faster and more elegant approach would be using SQLPS. (POSH)

This concept is useful when one knows that one has implemented in a given step of a job something handy. It happens that the only thing one can remember is that the job step contained something such as "DTEXEC ....", but certainly among so many servers and jobs within each, is an ocean of info that one would have to deal with.

So, it comes really handy to tap into all jobs and jobsteps on every server and immediately find, what one is looking for.

Post #1286097

Sunil Chandurkar

Posted Thursday, April 19, 2012 6:20 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, September 27, 2012 4:24 AM
Points: 253, Visits: 78

Jorge Serres (4/18/2012)

Thanks for yout suggestion! Definitely it could be extended as per the requirement.

Post #1286350

« Prev Topic | Next Topic »

Permissions