SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Sunil Chandurkar / Find for SQL Objects, Entities or Text / Latest Posts

RE: Find for SQL Objects, Entities or Text

Sunil Chandurkar — Thu, 19 Apr 2012 06:20:58 GMT

[quote][b]Jorge Serres (4/18/2012)[/b][hr]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.[/quote]Thanks for yout suggestion! Definitely it could be extended as per the requirement.

RE: Find for SQL Objects, Entities or Text

Jorge Serres — Wed, 18 Apr 2012 15:07:21 GMT

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.

RE: Find for SQL Objects, Entities or Text

fraymond 69364 — Wed, 31 Mar 2010 08:25:33 GMT

I created some simpler queries in my blog [url]http://geekatwork.wordpress.com/2010/03/30/database-schema-search/[/url]Search for Text in Stored ProceduresDECLARE @SEARCH_TERM VARCHAR(100)SET @SEARCH_TERM = ‘%BANKCARD_999%’SELECT ROUTINE_NAME, ROUTINE_DEFINITIONFROM INFORMATION_SCHEMA.ROUTINESWHERE ROUTINE_DEFINITION LIKE @SEARCH_TERMORDER BY ROUTINE_NAMESearch for Text in All ColumnsDECLARE @SEARCH_TERM VARCHAR(100)SET @SEARCH_TERM = ‘%BANKCARD_999%’SELECT TABLE_NAME, COLUMN_NAMEFROM INFORMATION_SCHEMA.COLUMNSWHERE COLUMN_NAME LIKE @SEARCH_TERMORDER BY TABLE_NAME, COLUMN_NAMESearch for Text in All Database ObjectsDECLARE @SEARCH_TERM VARCHAR(100)SET @SEARCH_TERM = ‘%BANKCARD_999%’SELECT o.NAME, c.TEXTFROM sysobjects oINNER JOIN syscomments cON o.id = c.idWHERE c.TEXT LIKE @SEARCH_TERMo.TYPE IN (‘P’, — Stored Procedure‘V’, — View‘FN’, — Function,‘PK’ — Key)

RE: Find for SQL Objects, Entities or Text

miguel1974 — Wed, 14 Jan 2009 11:49:32 GMT

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 DefinitionFROM sys.all_objects WHERE NOT(object_definition(object_id) IS NULL)Keep up the good work.Miguel

RE: Find for SQL Objects, Entities or Text

Sunil Chandurkar — Sun, 27 Jul 2008 22:49:17 GMT

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?

RE: Find for SQL Objects, Entities or Text

Rudy Panigas — Mon, 07 Jul 2008 06:46:42 GMT

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

RE: Find for SQL Objects, Entities or Text

Sunil Chandurkar — Sat, 05 Jul 2008 06:08:45 GMT

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.

RE: Find for SQL Objects, Entities or Text

Rudy Panigas — Thu, 03 Jul 2008 09:13:41 GMT

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

Find for SQL Objects, Entities or Text

Sunil Chandurkar — Wed, 28 May 2008 14:02:38 GMT

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