glad it's helping you out Vincent;
you know that's a neat idea and exactly why i love SSC; other peoples feedback make everything better.
Sometimes you cannot see the tree when you are standing in the forest: extending sp_getDDL to return any object definition just makes sense. I have an updated version of that that gets the definition of any regular or TEMP table (#tempname) as well;
also i have another version that splits the text results of the varchar(max) on vbCrLf, and returns a multi-row resultset; i fiddle with this thing all the time to make it better.
As for the CrLf issue, i suspect that the problem is this:
I think that maybe the text is syscomments is delimited with just CHAR(13), and not CHAR(13) + CHAR(10);
i think that i would do a double replace to fix it and see:
SET @FINALSQL = 'SELECT S.TEXT FROM SYSCOMMENTS....
--becomes
SET @FINALSQL = SELECT REPLACE(REPLACE(S.TEXT,CHAR(13),CHAR(13) + CHAR(10)), CHAR(13) + CHAR(10) + CHAR(10),CHAR(13) + CHAR(10) ) FROM SYSCOMMENTS S [more]
i'm going to play with your example, i might change it so it hits sys.sql_modules instead of syscomments, but that is an awesome idea, and thank you so much for the feedback!
Lowell