Search in your T-SQL objects

  • Wilfred van Dijk

    SSCrazy Eights

    Points: 8967

    Comments posted to this topic are about the item Search in your T-SQL objects

    Wilfred
    The best things in life are the simple things

  • JStiney

    Say Hey Kid

    Points: 671

    Wilfred,

    I get the following message on the select statement when I inserted

    set @SearchCriteria = '%MEAG_MASTER%' and executed the script.

    Any suggestions?

    Msg 170, Level 15, State 1, Line 5

    Line 5: Incorrect syntax near '?'.

    Line 5 is the select.

  • rVadim

    Hall of Fame

    Points: 3911

    I've got that error too. When pasted in Notepad++ it looks like this:

    select????distinct

    ????????@SearchCriteria as ZoekCriteria

    ????????, 'in code' as Classification

    ????????, b.type_desc

    ????????, object_name(a.id) "ObjectName"

    ????????, b.modify_date

    from????syscomments a

    inner????join sys.objects b

    on????????a.id = b.object_id

    where????a.encrypted = 0

    and????????a.text like @SearchCriteria

    --Vadim.

    --Vadim R.

  • JStiney

    Say Hey Kid

    Points: 671

    Thanks rVadim,

    I changed the ??? to blanks and the script works for me.

    declare @SearchCriteria nvarchar(100)

    set @SearchCriteria = '%MEAG_MASTER%' ---------> enter criteria here

    select distinct

    @SearchCriteria as ZoekCriteria

    , 'in code' as Classification

    , b.type_desc

    , object_name(a.id) "ObjectName"

    , b.modify_date

    from syscomments a

    inner join sys.objects b

    on a.id = b.object_id

    where a.encrypted = 0

    and a.text like @SearchCriteria

    union

    select @SearchCriteria

    , 'in code' as Classification

    , 'SYNONYM'

    , name

    , modify_date

    from sys.synonyms

    where base_object_name like @SearchCriteria

    union

    select @SearchCriteria

    , 'in code' as Classification

    , 'CHECK_CONSTRAINT'

    , name

    , modify_date

    from sys.check_constraints

    where definition like @SearchCriteria

    union

    select @SearchCriteria

    , 'in code' as Classification

    , 'DEFAULT_CONSTRAINT'

    , name

    , modify_date

    from sys.default_constraints

    where definition like @SearchCriteria

    union

    select @SearchCriteria

    , 'as columnname' as Classification

    , 'COLUMN'

    , object_name(object_id) collate database_default

    , NULL

    from sys.columns

    where name like @SearchCriteria

    union

    select @SearchCriteria

    , 'in objectname' as Classification

    , type_desc collate database_default

    , name collate database_default

    , modify_date

    from sys.objects

    where name like @SearchCriteria

    order by 1,2

    ;

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

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