May 28, 2008 at 2:02 pm
Comments posted to this topic are about the item Find for SQL Objects, Entities or Text
July 3, 2008 at 9:13 am
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'.
Rudy
July 5, 2008 at 6:08 am
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.
July 7, 2008 at 6:46 am
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
Rudy
July 27, 2008 at 10:49 pm
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?
January 14, 2009 at 11:49 am
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
March 31, 2010 at 8:25 am
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
)
April 18, 2012 at 3:07 pm
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.
April 19, 2012 at 6:20 am
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.
May 10, 2016 at 1:54 pm
Thanks for the script.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply