How to scan a database's Stored Procedures for a string

  • Thread starter Thread starter Mike Lopez
  • Start date Start date
M

Mike Lopez

Hello.

We're porting our SQL Server 7.0 databases to SQL Server 2000.

Some of the SQL in some of our SP's make reference to the "old" SQL Server
instance name. I need to change those references to use the new name.

I tried writing a VB app using ADOX, but unfortunately the OLEDB provider
for SQL Server does not support the Command property of the Procedure
object.

Other than manually opening each SP (there are a lot of them) and visually
scanning for the "old" server name, how can I programmatically perform this
scan?

Thanks in advance,

Mike
 
[snip]
Other than manually opening each SP (there are a lot of them) and visually
scanning for the "old" server name, how can I programmatically perform this
scan?
Click to expand...

Mike,

You could use Enterprise manager to script the stored procedures to a file,
and then either use the Find/Replace command in notepad or write a program
to search / replace the old server name. You can then use Query analyser to
recreate the stored procedures in your new db.

hth
andrew
 
Back
Top