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

[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

 

[Ivar]

SQL-DMO is way to go.

 

[andrew lowe]

[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?

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