Save Source Code with line breaks in Table

Question

Post reply

Save Source Code with line breaks in Table

MDave

SSC Eights!

Points: 953
More actions
September 15, 2009 at 8:11 am

#138865

Hello together,
i want to create a table where the source code of all my objects will be stored historically.
With this system view i get the source code, but there are no line breaks anymore and so i cannot reuse it anymore:
Select * from sys.sql_modules
With this procedure i get a row for each line in the object, but i want to have the sourcecode stored in only one column:
exec sp_helptext '@dbobject'
Can someone help me?
Thanks!
David

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

dbo.benyos SSCommitted Points: 1944 More actions · Answer 1

How about this one:

SELECT

CAST(CTEXT AS NVARCHAR(MAX))

FROM

SYSCOMMENTS

WHERE

OBJECT_NAME(ID) = 'YOUR_STORED_PROCEDURE_NAME_HERE'

Tal Ben Yosef
www.TalBenYosef.com
(visit my LinkedIn profile)

HowardW One Orange Chip Points: 29109 More actions · Answer 2

sys.sql_modules preserves line breaks, it's the SSMS grid view that doesn't. If you return the results to text, you'll see that the line breaks are there.

MDave SSC Eights! Points: 953 More actions · Answer 3

Hello Benyos,

thanks for your quick reply. Unfortunately it does not work, if you copy and paste the source code out there are still no linebrakes 🙁

Another idea?

MDave SSC Eights! Points: 953 More actions · Answer 4

MDave

SSC Eights!

Points: 953

September 15, 2009 at 8:38 am

#1053824

Thanks HowardW! Thats what i needed to know!

Garadin One Orange Chip Points: 29613 More actions · Answer 5

HowardW (9/15/2009)
sys.sql_modules preserves line breaks, it's the SSMS grid view that doesn't. If you return the results to text, you'll see that the line breaks are there.

I was just about to post this same thing. Your original method, Benyos method and the method I just made to concatenate sp_helptext into one line with line breaks all work fine if you switch to results in text. Query Analyzer also rips out the line breaks.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Garadin One Orange Chip Points: 29613 More actions · Answer 6

The history table you're making could likely be used with the DDL triggers in 2005 to be pseudo source control. Just make a DDL trigger for ALTER_PROCEDURE (Optionally create and drop as well) to insert from these system tables into your historical logging table.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

MDave SSC Eights! Points: 953 More actions · Answer 7

That's what i wanted to do. Every change to the database should be logged.

If you have source controll in subversion etc. there is still the possibility to make changes directy to the database, this should be avoided/tracked by this mechanism.

J-F Bergeron SSCrazy Eights Points: 8593 More actions · Answer 8

Hi,

I might be really out of bounds there, but.. have you considered using a Source manager like SourceSafe? Maybe I don't get your requirement correctly, but I find it a lot easier to use the free Microsoft tool for maintaining objects than creating a whole solution using a database for reviewing changes.

It's only my opinion, correct me if I misunderstood your needs.

Cheers,

J-F

Garadin One Orange Chip Points: 29613 More actions · Answer 9

David.Maron (9/15/2009)
That's what i wanted to do. Every change to the database should be logged.
If you have source controll in subversion etc. there is still the possibility to make changes directy to the database, this should be avoided/tracked by this mechanism.

I figured it was, figured I'd throw it out there anyways, just in case(and to keep it with the thread for anyone else reading it who may not be familiar with DDL triggers in 2005+).

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

MDave SSC Eights! Points: 953 More actions · Answer 10

The problems with sourcesafe, subversion are:

1. Changes can be made outside of the source control because they are based on flat files and there is no link to the database

2. Every change has to be logged into source control, database triggers do this automatically for you into a table in the database.

-> saves time, is much more secure.

MDave SSC Eights! Points: 953 More actions · Answer 11

In syscomments and sys.sql_modules there is only the code of procedures and views stored, is there also a possibilty to see the definition of tables and indizes?

dbo.benyos SSCommitted Points: 1944 More actions · Answer 12

Just join them the way you want:

--OLD CODE:

select * from INFORMATION_SCHEMA.COLUMNS

--NEW CODE

select * from sys.tables

select * from sys.columns

select * from sys.indexes

and there are planty more sys.* to query from. Have a look at

http://msdn.microsoft.com/en-us/library/ms189783(SQL.90).aspx

Tal Ben Yosef
www.TalBenYosef.com
(visit my LinkedIn profile)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

David.Maron (9/15/2009)
The problems with sourcesafe, subversion are:
1. Changes can be made outside of the source control because they are based on flat files and there is no link to the database
2. Every change has to be logged into source control, database triggers do this automatically for you into a table in the database.
-> saves time, is much more secure.

That's true for any piece of code - it's disconnected until you actually deploy it (that's a GOOD thing, by the way). Using security to restrict changes to the DB would help you get around this hurdle.

One thing your triggers will not do for you would be all of the rest of the functionality of a true source control system: versioning,comparison tools of previous versions, branching,merging,check-in/checkout, etc....

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?