August 23, 2006 at 9:11 am
In SQL 2000, is there a System table which house the contents/text of every stored proc?
I need to identify all stored procs which are calling another proc called sp_Insert_Widgets.
August 23, 2006 at 9:48 am
Syscomments. Here's a query to find which stored procedures contain a specific string:
select distinct so.name from sysobjects so
join syscomments sc on sc.id = so.id
left outer join syscomments sc1 on sc1.id = sc.id and sc1.colid = sc.colid + 1
where so.type = 'p'
and ( sc.text like '%string%'
or right(sc.text,500) + left(isnull(sc1.text,''),500) like '%string%'
)
order by so.name
Greg
Greg
August 23, 2006 at 10:04 am
thx -- this worked perfect... (now I need a system table housing DTS package contents in text format) !!!!
August 23, 2006 at 3:26 pm
Sorry, there's no such table. DTS packages are stored in the sysdtspackages table in msdb, but the package code itself is binary data stored in an image column.
I recall seeing some scripts on this site that will export all packages to vb files which you can open and search, if that's what you want to do.
Greg
Greg
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply