November 20, 2006 at 1:12 am
In SQL Serv2000, how can I get a listing of the contents of all my stored procedures? I can get the names of the procedures by using sp_stored_procedure, but I can not find a way to see the actual text body of the procedures.
Best Regards
Paul
November 20, 2006 at 1:57 am
Hi,
sp_helptext , will provide the content of a stored procedure.
Cheers
cheers
November 20, 2006 at 5:07 am
You can also do this by running the following query in query analyzer (after setting the options to display the results in text/save to a file etc.):
select so.name, sc.text from sysobjects so inner join syscomments sc on so.id = sc.id and so.xtype = 'P' order by so.id
For the text of just one sp you can filter query by name, view it through EM or the object browser in QA or use sp_helptext as ijaz suggests..the options abound..
**ASCII stupid question, get a stupid ANSI !!!**
November 21, 2006 at 7:06 am
You can also use the "Generate SQL Script" option thru EM by right-clicking on a database and selecting this option from "All Tasks". You'll be able to select which SPs to include and it'll generate a .sql text file.
November 21, 2006 at 7:35 am
Thank you all for good tips!
Best Regards
Paul
November 21, 2006 at 12:55 pm
Use sp_helptext. The text column in syscomments is nvarchar(4000) and stored procedures can be up to 128 MB in size. If the procedure is longer than 4000 characters, the output from syscomments will be truncated.
This query will show you if any procedures are at least as long 4000 characters:
select so.name, len(sc.text) As ProcLen
from sysobjects so
inner join syscomments sc on so.id = sc.id and so.xtype = 'P'
order by ProcLen Desc
I'm using SQL 2005, so I can take advantage of a new system function, object_definition(), which will give me the entire procedure defintion, not just the first 4000 characters. If I check the actual length of the stored procedures that show a length of 4000 in syscomments, I get the following:
Select Routine_Name, Len(object_definition(object_id(Routine_Schema + '.' + Routine_Name))) As ProcLen
From Information_Schema.Routines R
Where Routine_Type = 'Procedure'
And Exists(Select 1 From sysobjects so
inner join syscomments sc on so.id = sc.id and so.xtype = 'P'
Where so.name = R.Routine_Name
And len(sc.text) = 4000)
Routine_Name ProcLen
USP_MaintenanceRestoreDatabase 8755
USP_CreateDLView 10915
usp_MaintenanceGetBackup 5225
DBUTIL_GenerateCRUDProcedures 15948
xUSP_SystemSettingsINSERT 5820
xUSP_SystemSettingsUPDATE 5878
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply