SQL Server Performance Forum – Threads Archive
Versioning The SPs
Hi folks!I needd to keep track of the SPs and Views definitions that are changed within the development domains.
I used SYSCOMMENTS table to copy the text of SPs and Views into a repository table to keep track of older text if changed.
I use CHECKSUM function to check the code in syscomments and the one i’ve created for versioning; if text is changed, the code from syscomments is bakedup into the repository table.
NOW, i need a way to read the text as it was created by the developer rather than the format held in a single line in SYSCOMMENTS table. Also i’ve to deal with the multilines for a single procedure in SYSCOMMENTS exceeding the 8060 Bytes lenght.
SP_HELPTEXT also breaks the line for longer rows. Howdy! ——————————————————————-
A DBA seems to work only when the users can’t, so i scarcely work!
The text in syscomments is exactly what the developer wrote. It contains all of the formatting. Also, to deal with multiple rows in syscomments, concatenate the text column in the client software. All that being said, it is more appropriate to save the functions/procedures as separate script files and use version control on those files. Pulling the text of the objects from syscomments is analagous to reverse-engineering source code from compiled DLLs and EXEs. Keith Payne
Technical Marketing Solutions
www.tms-us.com
Hi. thanx for ur reply.
actually sycomments doesn’t seem to have exactly what the developer writes. i need a way to identify the formatting so that i could recognize the new row in order to view the text in readable format.
an example: create procedure usp_test
as
select top 1 *
from sysobjects
return
go — no if u select the text it comes in a single line; i need to view it the way i created it: select a.text from syscomments a join sysobjects b on a.id=b.id where b.name=’usp_test’
Howdy! ——————————————————————-
A DBA seems to work only when the users can’t, so i scarcely work!
I think sp_helptext ‘sp’ will show what you want Madhivanan Failing to plan is Planning to fail
quote:Originally posted by thebeginner
Hi. thanx for ur reply.
actually sycomments doesn’t seem to have exactly what the developer writes. i need a way to identify the formatting so that i could recognize the new row in order to view the text in readable format.
an example: create procedure usp_test
as
select top 1 *
from sysobjects
return
go — no if u select the text it comes in a single line; i need to view it the way i created it: select a.text from syscomments a join sysobjects b on a.id=b.id where b.name=’usp_test’
Howdy! ——————————————————————-
A DBA seems to work only when the users can’t, so i scarcely work!
actually sycomments doesn’t seem to have exactly what the developer writes. i need a way to identify the formatting so that i could recognize the new row in order to view the text in readable format.
an example: create procedure usp_test
as
select top 1 *
from sysobjects
return
go — no if u select the text it comes in a single line; i need to view it the way i created it: select a.text from syscomments a join sysobjects b on a.id=b.id where b.name=’usp_test’
Howdy! ——————————————————————-
A DBA seems to work only when the users can’t, so i scarcely work!
The newline characters are in there (they will print as two boxes in QA results). If you use the select in a client application and assign the column to a multi-line textbox, it will display correctly. You can also test for the newline characters in a stored proc that fetches the text from syscomments. A newline is CR+LF, or CHAR(13) + CHAR(10). You can use any of the TSQL string functions to find the end of line. Keith Payne
Technical Marketing Solutions
www.tms-us.com
]]>