SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Find for SQL Objects, Entities or Text


Find for SQL Objects, Entities or Text

Author
Message
Sunil Chandurkar
Sunil Chandurkar
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 81
Comments posted to this topic are about the item Find for SQL Objects, Entities or Text
Rudy Panigas
Rudy Panigas
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4338 Visits: 1325
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'.



Sunil Chandurkar
Sunil Chandurkar
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 81
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.
Rudy Panigas
Rudy Panigas
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4338 Visits: 1325
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



Sunil Chandurkar
Sunil Chandurkar
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 81
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?
miguel1974
miguel1974
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 381
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
fraymond 69364
fraymond 69364
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 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
)
Jorge Serres
Jorge Serres
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 201
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.
Sunil Chandurkar
Sunil Chandurkar
SSC-Addicted
SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)SSC-Addicted (471 reputation)

Group: General Forum Members
Points: 471 Visits: 81
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.
Iwas Bornready
Iwas Bornready
SSC-Dedicated
SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)SSC-Dedicated (31K reputation)

Group: General Forum Members
Points: 31940 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search