Find for SQL Objects, Entities or Text

  • Sunil Chandurkar

    Right there with Babe

    Points: 791

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

  • Rudy Panigas

    SSChampion

    Points: 10695

    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

  • Sunil Chandurkar

    Right there with Babe

    Points: 791

    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

    SSChampion

    Points: 10695

    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

  • Sunil Chandurkar

    Right there with Babe

    Points: 791

    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

    SSC Enthusiast

    Points: 106

    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

    Grasshopper

    Points: 13

    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

    Old Hand

    Points: 352

    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

    Right there with Babe

    Points: 791

    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.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply