Scripting using SQL DMO 2

[Ajb2528]

Hi!!

I am trying to use SQL DMO in a VB.Net project to script out selected stored procedures. What I would like is to have all comments stripped out of the selected stored procedure just leaving the T-SQL.

Has anyone got any ideas on how to go about this?

Regards,

Alan

 

[onpnt]

Do you want to do this for viewing reasons in your app only or is this something you want to do as a alter on the procedure itself?

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[Ajb2528]

I just want to remove all of the comment lines/blocks (ie /* , */ and --) and leave the T-SQL intact

 

[onpnt]

Take a look at the StoredProcedure object. Be warned that if you use the alter method it is not recommended. Just make sure you read the fine print as it will be removed in later versions

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[Ajb2528]

I've had a look at that and it will not do what I want. It returns the comment information when you script it out.

I'm using-

mServerText = objStoredProcedure.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default)

 

[onpnt]

What version of SQL Server are you working on?

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[Ajb2528]

SQL2000

 

[onpnt]

well, I had a nice reply for you on SMO but that's out the door.

From my previous reply on Alter() and simply removing comments in the SP all you need to do is .Replace() the blocks and or comment lines (--). Regular Expressions is going to be your most efficient and best method in .NET to get this done.

To give you a simple example of this lets look at everything commented as /* */

Regular Expressions are fairly straight forward. If you make it simple it will be. A easy reference is here and here

Although this really doesn't belong in the SQL forum I'll finish the thread but I recommend going to the VB.NET forum after this please. This type of question may have a fine line though.

So from scratch.
Create the server object, database object and stored procedure object

        Dim srv As SQLDMO.SQLServer = New SQLDMO.SQLServer
        Dim db As SQLDMO.Database = New SQLDMO.Database
        Dim sp As SQLDMO.StoredProcedure = New SQLDMO.StoredProcedure

The next step I'm sure is review for you. Simply get the stored procedure text so you have it in your programs memory to manipulate. In my example I have a database named "test", table named "num" and sp named "usr_test" (I liek the word test )

        srv.LoginSecure = True
        srv.Connect("[servername]")
        db = srv.Databases.Item("test")
        sp = db.StoredProcedures.Item("usr_test")

Again nothing special. The RegEx part is nothing special either (although sense I used test for everything I kept replacing the sp name )

To use a .Replace on RegEx you simply use that method in the RegEx as
RegEx.Replace(str,pattern,replace string)
Remember to rbing in the System.Text.RegularExpressions

for a pattern that will find comments such as /* test */ you could go with
Dim pattern As String = "/\*[a-z\s]{0,}\*/"

That pattern equates to
/* any text or spaces */ (note I didn't check for numbers and there are special characters, characters classes you can use. That's your job to look them up from the link

The {0,} says zero or more matches. remember that [a-z] will match only one letter and only lower case. Use A-Z etc. all in the link I posted above.

* is a quantifier in regular expressions thus you need to escape it with \ (same as in java, C# etc... for escaping char's)

Now that we've gotten through that the rest is straight forward.

Dim orgSP As String = sp.Text.ToString()
Dim alterSP As String = Regex.Replace(orgSP, pattern, "")
sp.Alter(alterSP)

You just altered you stored procedure replacing anything found enclosed in /* */ in lower case.

Congradulations and have fun!


_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[onpnt]

I may have took you actually wanting to alter the SP incorrectly and you just want to show this in the program. Any case just leave the later out and do what you will with the string left after your manipulations


_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[Ajb2528]

Thanks for the reply!

I could use SMO as we have SQL Server 2005 installed.

 

[onpnt]

You could use the same above with SMO and simply change the .Text to .TextBody.

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[Ajb2528]

onpnt,

Thanks for pointing me in the right direction! I finally go it to work by using the following:

Dim regex As Regex = New Regex( _
"", _
RegexOptions.IgnoreCase _
Or RegexOptions.Multiline _
Or RegexOptions.IgnorePatternWhitespace _
)

Dim pattern As String = "(\/\*(\s*|.*?)*\*\/)|(--.*)"

and...

sServerTextTemp = objStoredProcedure.Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default)

mServerText = regex.Replace(sServerTextTemp, pattern, "")

Have a star!!

Regards,

Alan

 

[onpnt]

I'm glad to see it helped and you took off with it.

Thank you for the star but I feel you deserve one as much as I for posting the pattern for someone to use later if the same or similar topic comes up

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}