Get DDL for any SQL 2005 table

  • Alan Burstein

    SSC Guru

    Points: 61079

    I still love this proc Lowell (I'm using your newest version of sp_GetDDLa). I know I've posted that in this thread before. I thought I'd take a moment to include an example of how I've used it lately (earlier today most recently) for to create a "build script" for migrating tables from here to there... 

    First - sometimes I do this for a lot of tables, other times  I have some tables with 100+ columns and lots of indexes and constraints. The Problem in SSMS is that, when I print the output it can get truncated. Someone named Tim Wiseman wrote a great proc to deal with this called LongPrint. Here's the proc:


    CREATE PROCEDURE dbo.LongPrint @string nvarchar(MAX)
    AS
    /*
    Source:
    https://ask.sqlservercentral.com/questions/3102/any-way-around-the-print-limit-of-nvarcharmax-in-s.html

    Example:
    exec LongPrint @string = 'This String Exists to test the system.'

    This procedure is designed to overcome the limitation in the SQL print command that causes
    it to truncate strings longer than 8000 characters (4000 for nvarchar).

    It will print the text passed to it in substrings smaller than 4000 characters. If there
    are carriage returns (CRs) or new lines (NLs in the text), it will break up the substrings
    at the carriage returns and the printed version will exactly reflect the string passed.

    If there are insufficient line breaks in the text, it will print it out in blocks of 4000
    characters with an extra carriage return at that point.
    If it is passed a null value, it will do virtually nothing.

    NOTE: This is substantially slower than a simple print, so should only be used when actually needed. */

    DECLARE
    @CurrentEnd bigint, /* track the length of the next substring */
    @offset tinyint  /*tracks the amount of offset needed */

    set @string = replace(replace(@string, char(13)+char(10), char(10)), char(13), char(10));

    WHILE LEN(@String) > 1 BEGIN
    IF CHARINDEX(char(10), @string) BETWEEN 1 AND 4000
    BEGIN
      SET @CurrentEnd = CHARINDEX(char(10), @String) -1;
      SET @offset  = 2;
    END
    ELSE
    BEGIN
      SET @CurrentEnd = 4000;
      SET @offset  = 1;
    END; PRINT SUBSTRING(@String, 1, @CurrentEnd);

    SET @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822);

    END /*End While loop*/
    GO

    Here's a script I wrote today for grabbing a group of tables and throwing them into a build script. 

    -- (1) A table to hold your list of tables
    IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables;
    GO
    SELECT i = IDENTITY(int,1,1), table_name
    INTO #tables
    FROM (VALUES ('table1'),('table2'),('table3')) x(table_name);

    -- (2) Routine to populate a variable with some with the DDL for multiple tables
    SET NOCOUNT ON;
    DECLARE
    @i tinyint  = 1,
    @ii tinyint  = (SELECT COUNT(*) FROM #tables),
    @ddl varchar(max) = '',
    @tbl varchar(1000);
    DECLARE @xx varchar(max) = '';

    -- the loop
    WHILE @i <= @ii
    BEGIN
    SET @tbl = 'dbo.'+(SELECT table_name FROM #tables WHERE i = @i); -- get the next table in the list

    DECLARE @x TABLE (x varchar(8000)); -- I need this for an INSERT EXEC

    INSERT @x
    EXEC dbo.sp_GetDDLa @tbl;
    SET @xx +=
      STUFF(CAST((SELECT char(10)+x FROM @x FOR XML PATH(''), TYPE)
      as varchar(max)),1,1,'')+'GO'+char(10);

    SET @i += 1;
    DELETE FROM @x;
    END;

    -- (3) Required (remove this to see why I use it)
    SET @xx = REPLACE(@xx, ')GO', ')'+char(10)+'GO');

    -- (4) Print the results in SSMS
    EXEC dbo.LongPrint @xx; -- LongPrint allows me to print my DDL without it getting truncated
    GO

    -- cleanup
    DROP TABLE #tables;
    GO

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • rgilland1966

    SSC Rookie

    Points: 25

    Using http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt on SQL Server 2008 I get the following error in trying to compile the procedure sp_GetDDL
    Msg 102, Level 15, State 1, Procedure sp_GetDDL, Line 882
    Incorrect syntax near 'obj'.

  • m.martinelli

    SSC Enthusiast

    Points: 133

    Probably the database in wich you try to compile is with compatibility level 80 (sql server 2000).
    The cross apply work only from compatibility level 90 (sql server 2005).
    Try to compile it in MASTER db.

  • jhzhao88

    Valued Member

    Points: 59

    Lowell - Monday, April 10, 2017 10:51 AM

    For those of you subscribed to this thread, I've made some fixes today for a couple of things i found.
    These changes are  noted in section V3.17.
    When scripting foreign keys, the column name(s) were not quotenamed, so in the rare instance where you had column names that had spaces, dashes, or started with a number, the script was invalid.
    Secondly, I'm now 2016 and especially using ColumnStore Indexes pretty regularly, and the old script would create an invalid syntax for the column  store index. 

    I plan on adding the scripting of memory optimized tables, but it's not there yet.

    So.....

    Hi Lowell, thank you so much for the great work. I have been using it as a function and it works very well.
    I do have one issue: The stored procedure/view/function (not table) are created as their very original names. If I change the name from "lighting.pr_Ameren_Store_Comm_report" to 'lighting.pr_report', it does not script with the name "lighting.pr_report", instead it always creates the original name proc. From what I can see, the system does not update to the new name as the definition in sys.sql_modules and the text in sys.syscomments are still using the original names.I wonder if there is a way we can fix it...

    The script for this case is "IF OBJECT_ID('[lighting].[pr_report]') IS NOT NULL DROP PROCEDURE [lighting].[pr_report] GO CREATE PROC [lighting].[pr_Ameren_Store_Comm_report] AS ..." .

  • jhzhao88

    Valued Member

    Points: 59

    Lowell - Monday, April 10, 2017 10:51 AM

    For those of you subscribed to this thread, I've made some fixes today for a couple of things i found.
    These changes are  noted in section V3.17.
    When scripting foreign keys, the column name(s) were not quotenamed, so in the rare instance where you had column names that had spaces, dashes, or started with a number, the script was invalid.
    Secondly, I'm now 2016 and especially using ColumnStore Indexes pretty regularly, and the old script would create an invalid syntax for the column  store index. 

    I plan on adding the scripting of memory optimized tables, but it's not there yet.

    So.....

    I was wondering if you can add something for system-versioned temporal table. We have quite a lot of temporal tables. Thank you very much for your great work.

  • Lowell

    SSC Guru

    Points: 323450

    jhzhao88 - Wednesday, December 20, 2017 3:56 PM

    Lowell - Monday, April 10, 2017 10:51 AM

    quotey thing just submitted. sorry, see below.

    Hi Lowell, thank you so much for the great work. I have been using it as a function and it works very well.
    I do have one issue: The stored procedure/view/function (not table) are created as their very original names. If I change the name from "lighting.pr_Ameren_Store_Comm_report" to 'lighting.pr_report', it does not script with the name "lighting.pr_report", instead it always creates the original name proc. From what I can see, the system does not update to the new name as the definition in sys.sql_modules and the text in sys.syscomments are still using the original names.I wonder if there is a way we can fix it...

    The script for this case is "IF OBJECT_ID('[lighting].[pr_report]') IS NOT NULL DROP PROCEDURE [lighting].[pr_report] GO CREATE PROC [lighting].[pr_Ameren_Store_Comm_report] AS ..." .

    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!

  • Lowell

    SSC Guru

    Points: 323450

    the naming thing is caused by using the sp_rename command; there are a few questions of the day on the same issue.

    when you use sp_rename on a view/procedure/function, the original definition remains untouched in sys.sql_modules,  while the name in sys.objects is modified.
    However, when you script via smo/ssms, it is nice enough to swap the name out for you, but I had not considered it important previously. it would be some sort of find and replace, but I could not know, programmatically, where the
    /* lots of comments*/
    CREATE PROC / CREATE PROCEDURE exists in the script definition, and considering whitespace between those multiple variations would exist in the object_definition; it's not a simply replace oldvalue/newvalue. I am not actually parsing the procedure into tokens the way you can in a programming language.

    I'll spend a little time looking, but it's not an easy fix.

    As far as the temporal tables related to the existing table, yes, that is actually pretty easy to do. I'll start looking at this again and will post after testing. I have a number of other unpublished changes I made recently to add tot eh current version.

    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!

  • jhzhao88

    Valued Member

    Points: 59

    Hi Lowell,

    I am currently using find and replace to handle the rename issue as I don’t always have permissions to drop and recreate in production. It is not a big deal though.

    I am glad the temporal table scripting is an easy fix. Currently I just add a script in the end to define the system time and turn on the system versioning if the property says it’s a temporal table. I am looking forward to the new update.

    I love the return table DDL version as well.

    Thanks a lot for the quick reply and the great work!

    - - Xiao

  • clone.abhi

    Newbie

    Points: 5

    Lowell - Monday, October 19, 2009 12:48 PM

    ok I was playing with this today and found that i somehow broke the code for an computed columns;I fixed and tested the solution, and here we go again:here is the updated version: sp_GetDDL2005_V306 fixes that, and also tweaks some alignment issues for when a column is defined as a decimal.the join for sys.computed_columns was completely missing, as well as the .definition...i know it worked in the cursor version.Thanks for making me look at this again, guys.

    Hey can you please update the link, I am not able to download your updated file. Seems like the site you uploaded is down,

  • Lowell

    SSC Guru

    Points: 323450

    all those interim versions from 5+ years ago are gone,and only the latest are maintained.
    these are the links, which is @ version 3.18, i need to post my latest changes and improvements again soon,  a lot of improvements are in versions 3.19 and 3.20.
    When i do, it's the same links anyway.

    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!

  • jhzhao88

    Valued Member

    Points: 59

    Lowell - Thursday, February 21, 2019 9:41 AM

    all those interim versions from 5+ years ago are gone,and only the latest are maintained.
    these are the links, which is @ version 3.18, i need to post my latest changes and improvements again soon,  a lot of improvements are in versions 3.19 and 3.20.
    When i do, it's the same links anyway.

    sp_GetDDLa_Latest.txt (Returns Table) 
    sp_GetDDL_Latest.txt (Returns varchar(max) )

  • jhzhao88

    Valued Member

    Points: 59

    jhzhao88 - Friday, February 22, 2019 4:55 PM

    Lowell - Thursday, February 21, 2019 9:41 AM

    all those interim versions from 5+ years ago are gone,and only the latest are maintained.
    these are the links, which is @ version 3.18, i need to post my latest changes and improvements again soon,  a lot of improvements are in versions 3.19 and 3.20.
    When i do, it's the same links anyway.

    sp_GetDDLa_Latest.txt (Returns Table) 
    sp_GetDDL_Latest.txt (Returns varchar(max) )

    Could you please include the system versioned / temporal table script? That will be great! Thanks a lot.
    Xiao

Viewing 12 posts - 106 through 117 (of 117 total)

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