Get DDL for any SQL 2005 table

  • Lowell

    SSC Guru

    Points: 323442

    Comments posted to this topic are about the item Get DDL for any SQL 2005 table

    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: 323442

    sp_getDDL returns the complete CREATE TABLE command in TSQL; I've been using it in a DDL audit trigger, and also using it more often then sp_help.

    Could some of you folks please test drive my contribution a bit? a extrapair of eyes would help flesh out any mistakes or sections that need improvement.

    I updated this procedure so that it additionally returns extended properties about the table in question.

    here's some sample output:

    CREATE TABLE [dbo].[TBSTATE] (

    [STATETBLKEY] INT NOT NULL,

    [INDEXTBLKEY] INT NOT NULL,

    [STATECODE] CHAR(2) NOT NULL,

    [STATENAME] VARCHAR(50) NOT NULL,

    [FIPS] CHAR(2) NULL,

    CONSTRAINT [PK__TBSTATE__17A421EC] PRIMARY KEY CLUSTERED (STATETBLKEY),

    CONSTRAINT [STATECODEUNIQUE] UNIQUE NONCLUSTERED (STATECODE))

    GO

    EXEC sys.sp_addextendedproperty

    @name = N'FIPS', @value = N'the FIPS code is a two digit numeric-only varchar with preceeding zeros assigned by the government',

    @level0type = N'SCHEMA', @level0name = [dbo],

    @level1type = N'TABLE', @level1name = [TBSTATE];

    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!

  • Nathan Kilgore

    SSC Enthusiast

    Points: 164

    Let me first say that this is a wonderful bit of code.

    I am looking for fixes to any of the following issues I have encountered:

    1. Identity issue "not for replication" is missing

    2. Identity if the type is anything other than an Integer no identity is created

    3. When column type is user defined or SYSNAMES they end up as nvarchar

    Thank you.

  • Lowell

    SSC Guru

    Points: 323442

    Nathan thanks for the feedback; having a different pair of eyes helps so much shaking down a proc like this.

    #2 and #3 on the list of the things you identified I could correct immediately: all the int types were working(tinyint/smallint/bigint), and i added decimal and numeric; i think that is the complete list of allowable identity column types, right?

    #3 was selecting a different column: user_type_id immediately fixed the type_name issue.

    you can download the latest version here

    for item #1, i know where the command *should* go in the script, but i don't know how to identify the field:

    use this code as an example:

    create table blah(

    blahid decimal(18,0) identity(1,1) NOT FOR REPLICATION primary key ,

    blahname sysname)

    select * from sys.columns where object_name(object_id)='blah'

    exec sp_getddl blah

    drop table blah

    if you look at the columns in sys.columns, I see several candidates for identifying "NOT FOR REPLICATION":

    is_replicated

    is_non_sql_subscribed

    is_merge_published

    is_dts_replicated

    I don't know IF those columns are used to identify that property or not...never do replication at my shop.

    if you have a replicated database, could you tell me which might be the one?

    since I never replicate, i don't see the advantage of adding that optional command; i similarly skipped saying what file group and some other things related to the table as well.

    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!

  • Nathan Kilgore

    SSC Enthusiast

    Points: 164

    Lowell,

    Thank you very much for the fast reply. It is funny I went ahead and coded it for the other numeric data types that can be used as Identity and our code is very near identical. I am going to look at the replicated database that I have and do a comparison to come up with the correct field.

    Thank you again for the fast response.

    Nathan

  • sarodude

    SSC Journeyman

    Points: 81

    Lowell-

    V305 appears to have a bug which prevents compilation at line 121.

    + SPACE(2) + WHEN sys.columns.[is_nullable] = 0

    It looks like that section was the subject of revision compared with V304.

    (edit) Forgot to mention that removing "+ WHEN sys.columns.[is_nullable] = 0" seems to allow compilation.

    -Saro

  • Elliott Whitlow

    SSC Guru

    Points: 102296

    Lowell,

    COLUMNPROPERTY('IsIdNotForRepl') should return 0/1/NULL

    I'd look over COLUMNPROPERTY, it has a bunch of useful information..

    CEWII

  • Lowell

    SSC Guru

    Points: 323442

    Elliot thanks, I'm looking at it now, if i get it to work i'll add another version;

    sarodude, thanks! i had it working, then was trying to find the right column in sys.columns, added some sample code, and then saved it....my fault.

    i fixed it again so it's compiling correctly.

    thanks everyone for input on my mini-project.

    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!

  • Elliott Whitlow

    SSC Guru

    Points: 102296

    Well when I release my auditing and logging "project" on Codeplex you guys can take it apart..

    CEWII

  • Lowell

    SSC Guru

    Points: 323442

    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.

    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!

  • David In BC

    SSC-Addicted

    Points: 466

    There's a bug in this script at approximately line 82 - details below.

    At line 56, you go to the trouble of figuring out the OBJECT_ID for the table using the schema name, etc:

    SELECT

    @TABLE_ID = [object_id]

    FROM sys.objects

    WHERE [type] IN ('S','U','V')

    AND [name] <> 'dtproperties'

    AND [name] = @TBLNAME

    AND [schema_id] = schema_id(@SCHEMANAME) ;

    And then a bit farther down, you cancel out all that good work, and re-set the TABLE_ID. If your table is in a schema other than the dbo schema, the @TABLE_ID gets set to NULL, and the stored procedure returns no results.

    -- Valid Table, Continue Processing

    SELECT @FINALSQL = 'CREATE TABLE [' + @SCHEMANAME + '].[' + UPPER(@TBLNAME) + '] ( '

    SELECT @TABLE_ID = OBJECT_ID(@TBLNAME)

    David

  • Lowell

    SSC Guru

    Points: 323442

    wow excellent catch David! I rarely work under multiple schemas, and certainly missed this!

    I've updated my code and previous links to have the correction you mentioned, and you can downloaded it here if needed:

    here is the updated version: sp_GetDDL2005_V307

    I've got a function that uses the same code, I'll need to fix that as well.

    Thanks David!

    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!

  • David In BC

    SSC-Addicted

    Points: 466

    No problem, and thanks for the excellent script.

    I am planning to use a modified version to help manage our internal database version control - roll up patch directories back into the main branch, and I needed something to pull out all the tables.

    David

  • Lowell

    SSC Guru

    Points: 323442

    yeah i've used this code for a "schema checker" which compares a database to various xml docs;

    based on an internal table, if your db is marked "version 4" or "version4.1", etc , it uses the appropriate xml doc from a website to check for objects;anything missing in the target database gets the script that was generated from this proc; works well for me;

    I even have a version generates the results to create the same table in an Oracle database...creates triggers for the identity(), changes datatypes to NUMBER,VARCHAR2, etc.

    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!

  • SQLRNNR

    SSC Guru

    Points: 281205

    Thanks Lowell for a superb script.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 15 posts - 1 through 15 (of 117 total)

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