• 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!