Get DDL for any SQL 2005 table

  • Thanks! I made the change as well. Hopefully this can be added to the latest version.

  • This change needs to be in the latest version. I see the issue as well.

  • spcghst440 / Tim C, thank you very much for pointing out that gap!

    I've been using this for literally a decade or more, and so far , in my environment, i had not tripped over a object referencing a table not in it's same schema. great catch.

    I have updated the links above to have the latest changes spcghst440 pointed out.

    the things that are still technically a gap that i know of are:

    1. if a table is partitioned, i think it does not return the table definition, but that may be related to item 2 below.
    2. if the database has any custom data types with really long names, it breaks the scripting/formatting  logic that is looking for the largest data type, and ends up nulling out the column list. i was grabbing the length to make the formatting of columns/datatypes/null align and look pretty, and something in there breaks my logic.

    i use this proc as a keyboard shortcut literally hundreds of times per week. it's helped me for years. I hope it helps you too.

     

    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!

  • oh yeah, and stubborn old me only likes varchars..so if you have a table that has a name with nvarchar, like  below, you'd have to find and replace all the varchar statements to nvarchars to get it to work:

    CREATE TABLE [私のテーブル]
    ([Id] INT ,
    [列名] NVARCHAR(30) )

    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!

  • Thanks for the quick fix Lowell! I too use this proc as a keyboard shortcut for years on daily basis. It has been very helpful. We do have RedGate to get the definitions for most of the tables but the system versioning table scripting are not added to RedGate yet. So I will keep using this one as it is very convenient.

    Xiao

  • Azure managed instance ... Could not mark system object... Could not find stored procedure 'sys.sp_MS_marksystemobject'. Is there a work around?

    https://learn.microsoft.com/en-us/answers/questions/146642/azure-managed-instance-could-not-find-stored-proce

    Thanks,

    Xiao

     

    • This reply was modified 4 months, 3 weeks ago by  jhzhao88.
    • This reply was modified 4 months, 3 weeks ago by  jhzhao88.
  • In Azure, the procedures sp_GetDDL and sp_GetDDLa would have to be installed in each database; so 10 dbs = 10 copies, sorry

    we don't have the ability to leverage ability to mark a procedure as a system object at all in Azure SQL databases.

    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!

  • Thanks Lowell ! Appreciate the quick response.

Viewing 8 posts - 121 through 127 (of 127 total)

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