How can i Modify/edit scripts?

Question

How can i Modify/edit scripts?

Smoke

Old Hand

Points: 308
More actions
January 23, 2004 at 11:01 am

#161096

G'd Afternoon:
I would like to know if some one can tell me if is possible to edit an object using SQL-DMO. I'm asking this because i need to change many field names in a few tables but my problem is that those fields exist in LOT of sprocs, after many tries i found the way to get the name and the script of each one of the sprocs where those fields appear.
My question is, can i manipulate those scripts and change replace the "wrong" field name with the "right ones"?. If the answer is yes, can you let me know how, or where can i find information to accomplish this task?
If the answer is no. Is there any other way to do it?
For any help/info, thanks in advance
Estuardo

Manifest plainness,Embrace simplicity,Reduce selfishness,Have few desires.
Lao-t'ze.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

It wouldn't be too difficult to use DMO to script out the procedures (ORing SQLDMOScript_Drops), use your language to replace the column names, and then drop and recreate the relevant procs from the edited scripts. Personally, I'd use this:

http://www.moshannon.com/speedferret.html

--Jonathan

Smoke Old Hand Points: 308 More actions · Answer 2

Jonathan:

First of all i want to thank, your quick reply, and if i'm not asking too much, i would like to know if you can tell me where can i find examples about that part of "Recreate" that is exactly what i need to do but don't know how to do it. I'm working with vb, but i have no idea how can i drop and create a procedure from code, and i really would like to learn how to do it. If you know where can i find examples and/or information about it, i absolutelly will appreciate.

So far, i found many examples in how to create tables, jobs, backup's etc. but nothing to work with sprocs.

Regarding the application that you recomended... I thanx for the link, i already downloaded the application and i have no doubts i will use it, but first i would like to do it by myself.

Thanks for all your help

Estuardo

Manifest plainness,Embrace simplicity,Reduce selfishness,Have few desires.
Lao-t'ze.

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

Well, I wouldn't use this, so treat it as an example:

Sub AlterProcs(sDatabase As String, sOldColName As String, sNewColName As String)

Dim objServer As New SQLDMO.SQLServer

Dim objDatabase As SQLDMO.Database

Dim objProc As SQLDMO.StoredProcedure

Dim sScript As String

Const SERVER As String = "T23"

objServer.LoginSecure = True

objServer.Connect SERVER

Set objDatabase = objServer.Databases(sDatabase)

For Each objProc In objDatabase.StoredProcedures

sScript = objProc.Script(SQLDMOScript_Default)

If InStr(sScript, sOldColName) > 0 Then

sScript = Replace(sScript, "CREATE PROC", "ALTER PROC")

sScript = Replace(sScript, sOldColName, sNewColName)

objDatabase.ExecuteImmediate sScript

End If

One problem is that your old column name identifier may be part of another word. I suppose you could refine the replace (or use Instr) to check the relevant characters surrounding the string by looking for spaces, parentheses, commas, etc., e.g. Replace(sScript, " " & sOldColName & ",", " " & sNewColName & ",").

--Jonathan

Smoke Old Hand Points: 308 More actions · Answer 4

Woowww!!! Thanx a lot Jonathan... your code is outstanding!!

Thanks for your kind help.

Best regards

Estuardo

Manifest plainness,Embrace simplicity,Reduce selfishness,Have few desires.
Lao-t'ze.