Get DDL for any SQL 2005 table

  • Hi Lowell - I made a change which appears to have sorted out the comma, but I'd like to know whether removing the space padding might have som drawback which I haven't foreseen:

    I've commented out the space padding between column names in the indexes section, since your LEFT() was only removing one character, AFTER adding the space -

    SELECT QUOTENAME(COLS.[name]) + ',' --+ ' '

    Thanks,

    Kevin

  • kbleeker (10/18/2011)


    Hi Lowell - I made a change which appears to have sorted out the comma, but I'd like to know whether removing the space padding might have som drawback which I haven't foreseen:

    I've commented out the space padding between column names in the indexes section, since your LEFT() was only removing one character, AFTER adding the space -

    SELECT QUOTENAME(COLS.[name]) + ',' --+ ' '

    Thanks,

    Kevin

    Kevin you ROCK;

    that's it, i think it has to do with whether ANSI_PADDING is on or off.

    I was testing some more obscure things to try and recreate the scenario, and found an issue if your database was case Sensitive, the proc would not work, so i fixed that as well as adding the suggestion you identified...same links above, just updated.

    mea culpa on the error, should have seen that a long time ago.

    Thank you!

    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!

  • Haha! Thanks!

    Is it okay if I reply with another problem though? (Talk about a trial by fire, this DB is the devil!)

    4 columns in this DB are datatype "real", and the sp_GetDDLa is getting column widths for them, which of course leads to:

    Cannot specify a column width on data type real.

    For the first instance it generated a width of (24) - not sure if this helps, but I'm going through all of the generated scripts now to find the other instances of real, I'll let you know what I find.

  • ahh, i had seen that once before...i'll fix that right now.

    I can't thank you enough; another pair of eyes, doing work outside of my typical work load and comfort zone helps enourmously.

    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!

  • OK updated yet again...same old links but new code.

    sp_GetDDLa_Latest.txt (Returns Table)

    sp_GetDDL_Latest.txt (Returns varchar(max) )

    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!

  • Now that, sir, is a thing of beauty!

    Thanks so much for your ridiculously quick response, it's been a pleasure communicating with you!

    I am still having some problems with this demonic database with indexes and tables with the same name, but I'll not pester you with that now 🙂

    Let me show you my appreciation by sending you here http://suptg.thisisnotatrueending.com/archive/4805536/images/1244443242558.jpg

  • Hi Friends,

    I saw one issue with respect to the GET_DDL Script, issue is that NVARCHAR Data columns are Created with Double the Data Size of teh Actual Defined Size.

    Can you Please Check.

  • I can confirm this problem. Fix appears simple; change

    CONVERT(VARCHAR,(COLS.[max_length]))

    to

    CONVERT(VARCHAR,(COLS.[max_length] / 2))

    in two places.

  • Thanks elifestyle

  • Thanks for the input guys!

    Updated yet again...same old links but new code.

    sp_GetDDLa_Latest.txt (Returns Table)

    sp_GetDDL_Latest.txt (Returns varchar(max) )

    [/quote]

    this version is greatly enhanced;

    --scripts any object(table,proc, function,trigger)

    --has the nvarchar fix so they are not doubled

    --scripts #temp tables too!

    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!

  • I'm yet another user of this great procedure. Lowell, thanks for the work and sharing.

    I'm using it to (re)create tables from within a java app. I just have some issue with the GO statement , it always fails just after it.

    As a workaround I changed the procedure so that each GO is replaced by a semicolon ';' . That works for indexes but not for triggers('must be the first statement in a query batch').

    I guess the GO statement needs the 'newline' & 'carriage returns' before & after. Apparently the CHAR(10) & CHAR(13) are not recognized as such.

    Is there any user that solved such a problem ?

    Bart

    bartvdc

  • bartvdc (12/11/2012)


    I'm yet another user of this great procedure. Lowell, thanks for the work and sharing.

    I'm using it to (re)create tables from within a java app. I just have some issue with the GO statement , it always fails just after it.

    As a workaround I changed the procedure so that each GO is replaced by a semicolon ';' . That works for indexes but not for triggers('must be the first statement in a query batch').

    I guess the GO statement needs the 'newline' & 'carriage returns' before & after. Apparently the CHAR(10) & CHAR(13) are not recognized as such.

    Is there any user that solved such a problem ?

    Bart

    bartvdc

    good point Bart! everyone using anything other than SQL Server Management Studio would have the same problem;

    the GO is a batch separator for SSMS, and not an actual SQL Command; so you are doing it right by replacing it with semi colons., for sure.

    Glad this is helping you out!

    are you using the latest, which can also script any object or temp table?

    Latest sp_GetDDL_Latest.txt which returns a varchar(max) string

    Latest sp_GetDDLa_Latest.txt which returns a recordset

    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!

  • Yes , I'm using the latest version.

    Putting the semicolon instead of the GO gives the ''must be the first statement in a query batch'' problem for triggers.

    I will use the procedure 'as is' and split the result to different statements before sending them to the DB.

    Thanks for the quick reply,

    Bart

  • Just had this error when using the statement : Cannot specify a column width on data type real.

    I moved real to the group without length :

    Removed real here:

    WHEN TYPE_NAME(COLS.[user_type_id]) IN ('float') --,'real')

    Added real here:

    WHEN TYPE_NAME(COLS.[user_type_id]) IN ('datetime','money','text','image','real')

    Now it works.

    Bart

  • Nice procedure, saves me a lot of work.

    Thanks for sharing it.

    I have made some small improvement to add the order (asc,desc) of the index columns.

    Maybe you want to add it in your code too.

    Look for:

    SELECT COLS.[name] + ',' + ' '

    FROM sys.index_columns IXCOLS

    INNER JOIN sys.columns COLS

    ON IXCOLS.column_id = COLS.column_id

    AND IXCOLS.[object_id] = COLS.[object_id]

    WHERE IXCOLS.is_included_column = 0

    AND IDX.[object_id] = IXCOLS.[object_id]

    AND IDX.index_id = IXCOLS.index_id

    ORDER BY key_ordinal

    FOR XML PATH('')

    ) AS index_columns_key,

    and replace it with:

    SELECT COLS.[name] + case when IXCOLS.is_descending_key = 0 then ' asc' else ' desc' end + ',' + ' '

    FROM sys.index_columns IXCOLS

    INNER JOIN sys.columns COLS

    ON IXCOLS.column_id = COLS.column_id

    AND IXCOLS.[object_id] = COLS.[object_id]

    WHERE IXCOLS.is_included_column = 0

    AND IDX.[object_id] = IXCOLS.[object_id]

    AND IDX.index_id = IXCOLS.index_id

    ORDER BY key_ordinal

    FOR XML PATH('')

    ) AS index_columns_key,

    Just the first line (select part) was changed,

    the rest of the code ist for easier finding.

    Thanks again.

Viewing 15 posts - 46 through 60 (of 127 total)

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