Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Search in your T-SQL objects Expand / Collapse
Author
Message
Posted Wednesday, October 21, 2009 7:48 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:25 PM
Points: 930, Visits: 1,137
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
Post #806500
Posted Monday, November 16, 2009 10:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 2, 2012 7:05 AM
Points: 75, Visits: 446
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.



Post #819505
Posted Monday, November 16, 2009 11:50 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 12, 2014 2:16 PM
Points: 990, Visits: 2,223
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.
Post #819580
Posted Monday, November 16, 2009 12:22 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, November 2, 2012 7:05 AM
Points: 75, Visits: 446
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
;






Post #819602
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse