|
|
Posted by John Bell on 09/13/05 23:44
Hi
From sp_rename documentation in BOL:
"Renaming a stored procedure, view or trigger will not change the name of
the corresponding object name in the syscomments table. This may result in
problems generating a script for the object as the old name will be inserted
from the syscomments table into the CREATE statement. For best results, do
not rename these object types. Instead, drop and re-create the object by its
new name."
John
"laurenq uantrell" <laurenquantrell@hotmail.com> wrote in message
news:1126634202.405026.285820@z14g2000cwz.googlegroups.com...
> I'm using the function below to output all of my stored procedures into
> a text file. Fice, except that the output file does not reflect the
> names of the stored procedures correctly if the name has been changed.
>
> For example, I create a stored procedure named: "sp123" and then rename
> it to "sp123DELETE" or "sp123 DELETE" or "sp123OLD" or "sp123 OLD" and
> what I end up with is four entries in the output file all having the
> stored procedure name "sp123."
>
> I stop the service and restart before outputting the file.
>
> Any help is appreciated.
>
> lq
>
> Function ExportSP(myPath As String)
>
> Dim objSQLServer As New SQLDMO.SQLServer
> Dim dbs As New SQLDMO.Database
> Dim sp As SQLDMO.StoredProcedure
> Dim sptext As String
>
> objSQLServer.Connect <Servername>, <Username>, <Password>
> Set dbs = objSQLServer.Databases(<databasename>)
>
> Open myPath For Output As #1
>
> For Each sp In dbs.StoredProcedures
> sptext = sp.Text
>
> Print #1, sptext & _
> vbCrLf & vbCrLf & vbCrLf & _
> "*******" & _
> vbCrLf & vbCrLf & vbCrLf
> Next
>
> End Function
>
Navigation:
[Reply to this message]
|