June 13, 2008 at 1:03 pm
Does anyone know how to query all DB objects that contains a certain data?
For example, I'd like to query a string 'Address' and the result will be a list of objects that has the string 'Address' whether it is a data element or a column name.
Your help is appreciated.
Thank you.
June 13, 2008 at 1:05 pm
Try this.
CREATE TABLE #Tmp
(DatabaseName varchar(100),
ObjectName varchar(100),
Txt varchar(4000)
)
INSERT INTO #Tmp
EXEC(
'sp_msforeachdb''
USE ?
select ''''?'''', object_name(id) , [Text] from syscomments
where text like ''''%Address%''''
'''
)
Select * from #Tmp
Drop Table #Tmp
June 13, 2008 at 1:06 pm
Actually that will scan all databases. All you need is this.
select object_name(id) , [Text] from syscomments
where text like '%Address%'
June 13, 2008 at 1:13 pm
That's what I needed.
Thank you so much, I owe you one.:)
June 14, 2008 at 12:02 pm
I don't believe that will find table columns named "Address" will it?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 14, 2008 at 12:08 pm
No. I think they just need to find all objects such as views or sp's that contained the text Address.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply