Add comment block to existing procedures
-
I need to add a comment block to existing stored procedures which will be used for programming notes, date created,, etc...
What I have noticed is that in SYSCOMMENTS in the Nvarchar(4000) field the procedure may exist in chunks, a large procedure may have many chunks and my goal is to alter all procedures not containing the comment block by adding it to the begining of each procedure.
I have been able to pull all the procedures to another table and combine the comment block and source for the proc to a table with a text column (so I do not truncate any code), But my fear is when writting the larger procedures to the QA that there may occur some truncation due to the 8192 k limitation.
Does anyone have any other ideas on how I could accomplish this task without having to write these to QA and create scripts???
--Ron
-
Pretty sure you must have done this part already - Append the SET options and modify the column data to ALTER PROCEDURE.
You can then export the data in the text column out to a text file and then run that as a script.
Alternatively, if you have SQL 7 Query Analyzer, you can change the settings to display more than the usual 255 or 8000 characters. I believe it is under Tools-Options. The number you set it to, should be greater than
select max(datalength(TextColName)) from YourTable
Either way, please script out all the existing procedures.
Good luck.
-
Yes,
I have already appended the comment block, and added use DBNAME and GO's.
the max seting for query results is 8192
and I believe that is for the Analyzer.
I am working on sending results to a text file.
thx 4 the reply
--Ron
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply