Search in your T-SQL objects

• Wilfred van Dijk

  SSCrazy Eights

  Points: 9003

  More actions

  October 21, 2009 at 7:48 am

  #212859

  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

  More actions

  November 16, 2009 at 10:09 am

  #1079782

  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: 3969

  More actions

  November 16, 2009 at 11:50 am

  #1079843

  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

  More actions

  November 16, 2009 at 12:22 pm

  #1079859

  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

  ;