February 11, 2009 at 10:39 am
Does anybody know where SQL Server keeps the formated SQL for a stored procedure? I know where the SQL definitions are, but these are simply linear strings without any of the carrage return/line feeds that you put in when you create the SP. I want to automate pulling all of a DB's SP's out into text files for documentation purposes. I know SQL Server must keep the original somewhere. Every time you pull one up to modify it, it appears with the indentations and line breaks you put in to keep the logical segments more obvious. The SP definitions are stripped of everything but spaces. Any thoughts?
February 11, 2009 at 10:48 am
Hi,
syscolumns is the table where you will get the text of the stored procedures, would better go for some 3rd party tool for me to create a documents, you can download any of the redgate/idera tools and try them out....
Regards,
Sriram
Sriram
February 11, 2009 at 10:55 am
if you sp_helptext procedurename, it returns the text of the procedure, including all its comments, in the same format as wehn last created/altered.
if you do this in gridmode in SSMS, yeah it looks like one line...controlT to go to text mode, and you'll see the text the way you want to.
Lowell
February 11, 2009 at 11:03 am
The sp_helptext Sproc works great. Thanks.
February 11, 2009 at 11:12 am
The system views sys.sql_modules (and sys.all_sql_modules) will have the text of the stored procedure in the "definition" column...
An easy way to get text out of these tables is
DECLARE @sql nvarchar(max),@SPName sysname
SET @SPName = 'WhateverTheSPNameIs'
SELECT @sql = definition FROM sys.all_sql_modules
WHERE OBJECT_NAME(object_id) = @SPName
PRINT @sql
If you want to get these out into files - one way is to bcp out the query result into a text file:
bcp "SELECT definition FROM sys.sql_modules WHERE OBJECT_NAME(object_id) = 'StoredProcedureNameGoesHere'" queryout "C:\FullPathToFile\FileNameGoesHere" -S ServerNameGoesHere -T -c
February 11, 2009 at 11:15 am
As usual - I forgot the simplest method...use the "Generate Scripts" option in management studio to generate SP scripts by specifying one file per object... :Whistling:
February 11, 2009 at 11:44 am
Here's the Sub for those of you who might need it:
Sub subOutputStoredProcedures(strFilePath As String)
On Error GoTo subOutputStoredProcedures_err
Dim adocon As ADODB.Connection
Dim adoRecSetSQL_List As ADODB.Recordset
Dim adoRecSetSQL_Text As ADODB.Recordset
Dim strSQLName As String
Dim strDBName As String
Dim fsStream As Scripting.FileSystemObject
Dim tsTextDirList As Object
Dim tsTextOut As Scripting.TextStream
Dim strReadAll As String
Dim strSQL As String
' I kept this join in just for the hell of it. You only really
' need the sys.all_sql_modules for this.
strSQL = "SELECT [name] FROM sys.all_objects inner join sys.all_sql_modules " & _
"on sys.all_sql_modules.object_id = sys.all_objects.object_id " & _
"WHERE type_desc = 'SQL_STORED_PROCEDURE' AND " & _
"[name] like 'custsp%'"
Set fsStream = CreateObject("Scripting.FileSystemObject")
Set adoRecSetSQL_List = CreateObject("ADODB.Recordset")
Set adoRecSetSQL_Text = CreateObject("ADODB.Recordset")
Set adocon = New ADODB.Connection
adocon.CommandTimeout = 0
adocon.ConnectionString = YourConnection
adocon.Open
adoRecSetSQL_List.CursorLocation = adUseClient
adoRecSetSQL_List.Open strSQL, adocon, adOpenStatic, _
adLockReadOnly, adCmdText
Do While Not adoRecSetSQL_List.EOF
strSQL = "EXEC sp_helptext '" & adoRecSetSQL_List.Fields(0).Value & "'"
If Dir$(strFilePath & adoRecSetSQL_List.Fields(0).Value & ".txt") <> "" Then
Kill strFilePath & adoRecSetSQL_List.Fields(0).Value & ".txt"
End If
Set tsTextOut = fsStream.OpenTextFile(strFilePath & adoRecSetSQL_List.Fields(0).Value & ".txt", ForWriting, True)
adoRecSetSQL_Text.CursorLocation = adUseClient
adoRecSetSQL_Text.Open strSQL, adocon, adOpenStatic, _
adLockReadOnly, adCmdText
Do While Not adoRecSetSQL_Text.EOF
strSQL = adoRecSetSQL_Text.Fields(0).Value
tsTextOut.WriteLine strSQL
adoRecSetSQL_Text.MoveNext
Loop
tsTextOut.Close
adoRecSetSQL_Text.Close
adoRecSetSQL_List.MoveNext
Loop
adoRecSetSQL_List.Close
adocon.Close
Set tsTextOut = Nothing
Set fsStream = Nothing
subOutputStoredProcedures_exit:
If Not (adoRecSetSQL_List Is Nothing) Then
Set adoRecSetSQL_List = Nothing
End If
If Not (adoRecSetSQL_Text Is Nothing) Then
Set adoRecSetSQL_Text = Nothing
End If
If Not (adocon Is Nothing) Then
Set adocon = Nothing
End If
Exit Sub
subOutputStoredProcedures_err:
MsgBox "Applicaton Error: " & vbCrLf & _
vbCrLf & _
Error$ & vbCrLf & _
vbCrLf & _
"Error occured in: subOutputStoredProcedures", vbCritical, "Application Error Handling"
gbl_ErrorMsg = gbl_ErrorMsg & Error$ & vbCrLf & vbCrLf
Resume subOutputStoredProcedures_exit
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply