Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Find for SQL Objects, Entities or Text Expand / Collapse
Author
Message
Posted Wednesday, May 28, 2008 2:02 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 6:40 AM
Points: 253, Visits: 79
Comments posted to this topic are about the item Find for SQL Objects, Entities or Text
Post #508001
Posted Thursday, July 3, 2008 9:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:37 PM
Points: 312, Visits: 1,098
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
Posted Saturday, July 5, 2008 6:08 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 6:40 AM
Points: 253, Visits: 79
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
Posted Monday, July 7, 2008 6:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, July 18, 2014 12:37 PM
Points: 312, Visits: 1,098
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
Posted Sunday, July 27, 2008 10:49 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 6:40 AM
Points: 253, Visits: 79
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
Posted Wednesday, January 14, 2009 11:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 12:10 PM
Points: 63, Visits: 246
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
Posted Wednesday, March 31, 2010 8:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, April 18, 2012 3:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, June 26, 2014 5:33 PM
Points: 13, Visits: 150
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
Posted Thursday, April 19, 2012 6:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 6:40 AM
Points: 253, Visits: 79
Jorge Serres (4/18/2012)
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.

Thanks for yout suggestion! Definitely it could be extended as per the requirement.
Post #1286350
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse