Get DDL for any SQL 2005 table

  • Another quick fix - the size computation is missing for columns of type "varbinary" - I added this to the long query that computes the column type, length, and nullability:

    WHEN TYPE_NAME(sys.columns.[user_type_id]) = 'varbinary'

    THEN

    CASE WHEN sys.columns.[max_length] = -1

    THEN '(max)'

    + SPACE(6 - LEN(CONVERT(VARCHAR,(sys.columns.[max_length]))))

    + SPACE(7)

    + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

    + CONVERT(VARCHAR,(sys.columns.[max_length]))

    + ') '

    + SPACE(6 - LEN(CONVERT(VARCHAR,(sys.columns.[max_length]))))

    + SPACE(7)

    + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    There is also a bug in the nvarchar size handling - the "-1" isn't enough, as a NVARCHAR(MAX) has a length of 8000 - an inconsistency within SQL Server.... I haven't tackled that yet - just did a search and replace.

    David

  • Thanks for this script, it has been a great help!

    I extended the script with a few lines and now it returns also the DDL for views, procedures and functions.

    --##############################################################################

    -- Check If TableName is Valid

    --##############################################################################

    IF ISNULL(@TABLE_ID,0) = 0

    BEGIN

    SELECT @SCHEMANAME = ISNULL(PARSENAME(@TBL,2),'dbo') ,

    @TBLNAME = PARSENAME(@TBL,1)

    SELECT

    @TABLE_ID = [object_id]

    FROM sys.objects

    --WHERE [type] = 'U'

    WHERE [type] in ('V','P','FN' )

    AND [name] <> 'dtproperties'

    AND [name] = @TBLNAME

    AND [schema_id] = schema_id(@SCHEMANAME) ;

    IF ISNULL(@TABLE_ID,0) = 0

    BEGIN

    SET @FINALSQL = 'Table object [' + @SCHEMANAME + '].[' + UPPER(@TBLNAME) + '] does not exist in Database [' + db_name() + ']'

    SELECT @FINALSQL

    END

    ELSE

    BEGIN

    SET @FINALSQL = 'SELECT S.TEXT FROM SYSCOMMENTS S INNER JOIN SYS.OBJECTS O ON S.ID = O.OBJECT_ID WHERE O.NAME = ''' + @TBLNAME + ''''

    EXEC (@FINALSQL)

    --SELECT @FINALSQL

    END

    RETURN 0

    END

    Question: The CrLf is not working in the output, the characters do work if I run an print command of those chars (13, 10), but in the output there is no CrLF available. Should I convert to XML?

  • 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!

  • dayum that made for a really nice enhancement, Vincent.

    here's links to two updated versions of the script.

    sp_GetDDL V 309 returns the definition as a single varchar(max) of any of the main objects: Table, TempTable, View, Procedure, Function or Trigger.

    sp_GetDDLa V 309a returns the definition as one or more rows of data, the varchar(max) split on vbCrLf of any of the main objects: Table,TempTable, View, Procedure, Function or Trigger.

    here's a recap of some of the improvements made so far:

    -- USAGE: exec sp_GetDDLa [TableName] | [#TempTableName] |[ProcedureName] | [ViewName] [FunctionName] |[TriggerName]

    -- or exec sp_GetDDLa YourObjectName

    -- or exec sp_GetDDLa 'bob.example'

    -- or exec sp_GetDDLa '[schemaname].[ObjectName]'

    -- V300 uses String concatination and sys.tables instead of a cursor

    -- V301 enhanced 07/31/2009 to include extended properties definitions

    -- V302 fixes an issue where the schema is created , ie 'bob', but no user named 'bob' owns the schema, so the table is not found

    -- V303 fixes an issue where all rules are appearing, instead of jsut the rule related to a column

    -- V304 testing whether vbCrLf is better than just CHAR(13), some formatting cleanup with GO statements

    -- also fixed an issue with the conversion from syscolumns to sys.columns, max-length is only field we need, not [precision]

    -- V305 user feedback helped me find that the type_name function should call user_type_id instead of system_type_id

    -- also fixed issue where identity definition missing from numeric/decimal definition

    -- V306 fixes the computed columns definition that got broken/removed somehow in V300

    -- also formatting when decimal is not an identity

    -- V307 fixes bug identified by David Griffiths-491597 from SSC where the @TABLE_ID

    -- is reselected, but without it's schema , potentially selecting the wrong table

    -- also fixed is the missing size definition for varbinary, also found by David Griffith

    -- V308 abtracted all SQLs to use Table Alaises

    -- added logic to script a temp table.

    -- added warning about possibly not being marked as system object.

    -- V309 added logic based on feedback from Vincent Wylenzek @SSC to return the definition from sys.sql_modules for

    -- any object like procedure/view/function/trigger, and not just a table.

    -- note previously, if you pointed sp_GetDDL at a view, it returned the view as a table...

    -- now it will return the view definition instead.

    -- V309 returns single varchar(max)

    -- V309a returns multi row recordset, one line per record of the varchar(max) split on vbCrLf

    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 credits, but my input was only a few lines of code:)

    Your defenitely fast, thanks for providing the updates; I am working on an auditsystem

    where you can combine the sp_getdll with audit events.

    So every DDL event will be logged and the previous valid DDL-statement will be recorded by your statement.

    If it is finished I will post it here.

  • ahh kewl let me know how that works for you; that is one of the many reasons i made this proc( and a function that does the same thing)

    i had previously done something very similar with DDL audit triggers, but as i remember it, inside the audit trigger, the changes had already occurred, so i couldn't get the PREVIOUS DDL for an object that changed, only the current.

    that was minor in my opinion, since all i had to do was insert the sp_GetDDL of every current object as the basis for starting the audit trail. then it would be easy to compare the starting definition vs anything else the audit captured.

    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!

  • After 24Hrs of coding and searching for alternatives I almost gave it up but I finally get the DDL and previousversion DDL in a trigger working.

    I have got it working quit fast, but could not get rid of the 3609 - transaction ended message from the trigger.

    I tried the v309 code, but there are some errors in it. So I have manually configured your lines of new code in the previous version and extended it with an output into a variable.

    This variable is used within the trigger to write the result in an audit table.

    Currently I have got

    * One trigger which fires after every DDL statement within a database

    * The DDL procedure (which outputs a variable)

    * Two tables, one with the complete lines of code in one attribute (the new DDL and the previous in two seperate attributes) and another table, whereas all the lines of DDL are split up by your splitter. This table references the basetable.

    I am now working on a Cube and some reports to exploit these audit data in a friendly manner.

    All the code is here below. Sorry for the missing comments, I will provide these at the beginning of next week.

    The new SP_GETDDL

    -- USAGE: exec sp_GetDDL YourTableName

    -- or exec sp_GetDDL 'bob.example'

    -- or exec sp_GetDDL '[schemaname].[tablename]'

    --#############################################################################

    -- copyright 2004-2009 by Lowell Izaguirre scripts*at*stormrage.com all rights reserved.

    -- http://www.stormrage.com/Portals/0/SSC/sp_GetDDL2005_V307.txt

    -- V300 uses String concatination and sys.tables instead of a cursor

    -- V301 enhanced 07/31/2009 to include extended properties definitions

    -- V302 fixes an issue where the schema is created , ie 'bob', but no user named 'bob' owns the schema, so the table is not found

    -- V303 fixes an issue where all rules are appearing, instead of jsut the rule related to a column

    -- V304 testing whether vbCrLf is better than just CHAR(13), some formatting cleanup with GO statements

    -- also fixed an issue with the conversion from syscolumns to sys.columns, max-length is only field we need, not [precision]

    -- V305 user feedback helped me find that the type_name function should call user_type_id instead of system_type_id

    -- also fixed issue where identity definition missing from numeric/decimal definition

    -- V306 fixes the computed columns definition that got broken/removed somehow in V300

    -- also formatting when decimal is not an identity

    -- V307 fixes bug identified by David Griffiths-491597 from SSC where the @TABLE_ID

    -- is reselected, but without it's schema

    -- V-Vincent Wylenzek

    --Extended V307 with the new lines of code out of V309, V309 did not work here

    --Some other enhancements are:

    --* fixing the number of linefeeds in the output

    --* output everything to one single variable

    --* export the generated code of the list into a detailed audittable

    --NOTE: I ADDED A FIXED DB ZKS_AUDIT_DB WITH FIXED TABLES DATABASEAUDIT AND DATABASEAUDITDETAIL

    --HERE ARE THE SCRIPTS FOR THE TABLES:

    /*

    CREATE TABLE [dbo].[DatabaseAudit](

    [Audit_ID] [int] IDENTITY(1,1) NOT NULL,

    [AuditDate] [datetime] NOT NULL,

    [LoginName] [sysname] NULL,

    [EventType] [sysname] NULL,

    [SchemaName] [sysname] NULL,

    [ObjectName] [sysname] NOT NULL,

    [TSQLCommand] [nvarchar](max) NULL,

    [PreviousVersion] [nvarchar](max) NULL,

    [XMLEventData] [xml] NULL,

    PRIMARY KEY CLUSTERED

    (

    [Audit_ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[DatabaseAuditDetail](

    [Audit_ID] [int] NOT NULL,

    [Row_ID] [int] NOT NULL,

    [ObjectName] [nvarchar](255) NULL,

    [Row] [nvarchar](max) NULL,

    CONSTRAINT [PK_DatabaseAuditDetail] PRIMARY KEY CLUSTERED

    (

    [Audit_ID] ASC,

    [Row_ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    */

    -- You can use this however you like...this script is not rocket science, but it took a bit of work to create.

    -- the only thing that I ask

    -- is that if you adapt my procedure or make it better, to simply send me a copy of it,

    -- so I can learn from the things you've enhanced.The feedback you give will be what makes

    -- it worthwhile to me, and will be fed back to the SQL community.

    -- add this to your toolbox of helpful scripts.

    --#############################################################################

    --if you are going to put this in MASTER, and want it to be able to query

    --each database's sys.indexes, you MUST mark it as a system procedure:

    --EXECUTE sp_ms_marksystemobject 'sp_GetDDL'

    --#############################################################################

    ALTER PROCEDURE [dbo].[sp_GetDDL] (@TBL NVARCHAR(255), @Result NVARCHAR(MAX) OUTPUT)

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @TBLNAME NVARCHAR(200),

    @SCHEMANAME NVARCHAR(255),

    @STRINGLEN INT,

    @TABLE_ID INT,

    @FINALSQL NVARCHAR(max),

    @CONSTRAINTSQLS NVARCHAR(max),

    @CHECKCONSTSQLS NVARCHAR(max),

    @RULESCONSTSQLS NVARCHAR(max),

    @FKSQLS NVARCHAR(max),

    @TRIGGERSTATEMENT NVARCHAR(max),

    @EXTENDEDPROPERTIES NVARCHAR(max),

    @INDEXSQLS NVARCHAR(max),

    @vbCrLf CHAR(2) ,

    @input VARCHAR(max),

    @Audit_ID INT

    --##############################################################################

    -- INITIALIZE

    --##############################################################################

    --SET @TBL = '[DBO].[WHATEVER1]'

    --does the tablename contain a schema?

    SET @vbCrLf = CHAR(13) + CHAR(10)

    SELECT @SCHEMANAME = ISNULL(PARSENAME(@TBL,2),'dbo') ,

    @TBLNAME = PARSENAME(@TBL,1)

    insert into ZKS_AUDIT_DB.dbo.DatabaseAudit (Auditdate, SchemaName, ObjectName)

    SELECT GETDATE(), @SCHEMANAME, @TBLNAME

    set @Audit_ID = (select max(Audit_id) from ZKS_AUDIT_DB.dbo.DatabaseAudit where objectname = ''+ @TBLNAME+ '')

    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) ;

    --##############################################################################

    -- Check If TableName is Valid

    --##############################################################################

    IF ISNULL(@TABLE_ID,0) = 0

    BEGIN

    SELECT @SCHEMANAME = ISNULL(PARSENAME(@TBL,2),'dbo') ,

    @TBLNAME = PARSENAME(@TBL,1)

    SELECT

    @TABLE_ID = [object_id]

    FROM sys.objects

    --WHERE [type] = 'U'

    WHERE [type] in ('V','P','FN' )

    AND [name] <> 'dtproperties'

    AND [name] = @TBLNAME

    AND [schema_id] = schema_id(@SCHEMANAME) ;

    IF ISNULL(@TABLE_ID,0) = 0

    BEGIN

    SET @Result = ISNULL((@FINALSQL),'-N/A-')

    END

    ELSE

    BEGIN

    SET @FINALSQL = (SELECT S.TEXT FROM SYSCOMMENTS S INNER JOIN SYS.OBJECTS O ON S.ID = O.OBJECT_ID WHERE O.NAME = '' + ISNULL(@TBLNAME,'') + '')

    SET @Result = ISNULL((@FINALSQL),'-N/A-')

    SET @input = @FINALSQL

    --ten years worth of days from todays date:

    ;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows

    E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows

    E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows

    E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows

    --E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,

    Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),

    ItemSplit(

    ItemOrder,

    Item

    ) as (

    SELECT N,

    SUBSTRING(@vbCrLf + @input + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @input + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))

    FROM Tally

    WHERE N < DATALENGTH(@vbCrLf + @input)

    --WHERE N < DATALENGTH(@vbCrLf + @input) -- REMOVED added @vbCrLf

    AND SUBSTRING(@vbCrLf + @input + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter

    )

    --create table dbo.DatabaseAuditDetail (Audit_ID int, ObjectName nvarchar(255), Row nvarchar(max))

    insert into ZKS_AUDIT_DB.dbo.DatabaseAuditDetail (Audit_ID, Row_ID, ObjectName, Row )

    select @Audit_ID, row_number() over (order by ItemOrder) as ItemID, @TBLNAME, Item from ItemSplit

    END

    RETURN 0

    END

    --##############################################################################

    -- Valid Table, Continue Processing

    --##############################################################################

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

    --removed invalud cide here which potentially selected wrong table--thansk David Grifiths @SSC!

    SELECT

    @STRINGLEN = MAX(LEN(sys.columns.[name])) + 1

    FROM sys.objects

    INNER JOIN sys.columns

    ON sys.objects.[object_id] = sys.columns.[object_id]

    AND sys.objects.[object_id] = @TABLE_ID;

    --##############################################################################

    --Get the columns, their definitions and defaults.

    --##############################################################################

    SELECT

    @FINALSQL = @FINALSQL

    + CASE

    WHEN sys.columns.[is_computed] = 1

    THEN @vbCrLf

    + '['

    + UPPER(sys.columns.[name])

    + '] '

    + SPACE(@STRINGLEN - LEN(sys.columns.[name]))

    + 'AS ' + ISNULL(CALC.definition,'')

    ELSE @vbCrLf

    + '['

    + UPPER(sys.columns.[name])

    + '] '

    + SPACE(@STRINGLEN - LEN(sys.columns.[name]))

    + UPPER(TYPE_NAME(sys.columns.[user_type_id]))

    + CASE

    --IE NUMERIC(10,2)

    WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('decimal','numeric')

    THEN '('

    + CONVERT(NVARCHAR,sys.columns.[precision])

    + ','

    + CONVERT(NVARCHAR,sys.columns.[scale])

    + ') '

    + SPACE(6 - LEN(CONVERT(NVARCHAR,sys.columns.[precision])

    + ','

    + CONVERT(NVARCHAR,sys.columns.[scale])))

    + SPACE(7)

    + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN COLUMNPROPERTY ( @TABLE_ID , sys.columns.[name] , 'IsIdentity' ) = 0

    THEN ''

    ELSE ' IDENTITY('

    + CONVERT(NVARCHAR,ISNULL(IDENT_SEED(@TBLNAME),1) )

    + ','

    + CONVERT(NVARCHAR,ISNULL(IDENT_INCR(@TBLNAME),1) )

    + ')'

    END

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    --IE FLOAT(53)

    WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('float','real')

    THEN

    --addition: if 53, no need to specifically say (53), otherwise display it

    CASE

    WHEN sys.columns.[precision] = 53

    THEN SPACE(11 - LEN(CONVERT(NVARCHAR,sys.columns.[precision])))

    + SPACE(7)

    + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

    + CONVERT(NVARCHAR,sys.columns.[precision])

    + ') '

    + SPACE(6 - LEN(CONVERT(NVARCHAR,sys.columns.[precision])))

    + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    --ie NVARCHAR(40)

    WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('char','NVARCHAR')

    THEN CASE

    WHEN sys.columns.[max_length] = -1

    THEN '(max)'

    + SPACE(6 - LEN(CONVERT(NVARCHAR,sys.columns.[max_length])))

    + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

    + CONVERT(NVARCHAR,sys.columns.[max_length])

    + ') '

    + SPACE(6 - LEN(CONVERT(NVARCHAR,sys.columns.[max_length])))

    + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    --ie NNVARCHAR(40)

    WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('nchar','nNVARCHAR')

    THEN CASE

    WHEN sys.columns.[max_length] = -1

    THEN '(max)'

    + SPACE(6 - LEN(CONVERT(NVARCHAR,(sys.columns.[max_length]))))

    + SPACE(7)

    + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

    + CONVERT(NVARCHAR,(sys.columns.[max_length]))

    + ') '

    + SPACE(6 - LEN(CONVERT(NVARCHAR,(sys.columns.[max_length]))))

    + SPACE(7)

    + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    --ie datetime

    WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('datetime','money','text','image')

    THEN SPACE(18 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + ' '

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    --IE INT

    ELSE SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN COLUMNPROPERTY ( @TABLE_ID , sys.columns.[name] , 'IsIdentity' ) = 0

    THEN ' '

    ELSE ' IDENTITY('

    + CONVERT(NVARCHAR,ISNULL(IDENT_SEED(@TBLNAME),1) )

    + ','

    + CONVERT(NVARCHAR,ISNULL(IDENT_INCR(@TBLNAME),1) )

    + ')'

    END

    + SPACE(2)

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    + CASE

    WHEN sys.columns.[default_object_id] = 0

    THEN ''

    --ELSE ' DEFAULT ' + ISNULL(def.[definition] ,'')

    --optional section in case NAMED default cosntraints are needed:

    ELSE @vbCrLf + 'CONSTRAINT [' + def.name + '] DEFAULT ' + ISNULL(def.[definition] ,'')

    --i thought it needed to be handled differently! NOT!

    END --CASE cdefault

    --##############################################################################

    -- COLLATE STATEMENTS

    -- personally i do not like collation statements,

    -- but included here to make it easy on those who do

    --##############################################################################

    /*

    + CASE

    WHEN collation IS NULL

    THEN ''

    ELSE ' COLLATE ' + sys.columns.collation

    END

    */

    END --iscomputed

    + ','

    FROM sys.columns

    LEFT OUTER JOIN sys.default_constraints DEF

    on sys.columns.[default_object_id] = DEF.[object_id]

    LEFT OUTER JOIN sys.computed_columns CALC

    on sys.columns.[object_id] = CALC.[object_id]

    and sys.columns.[column_id] = CALC.[column_id]

    Where sys.columns.[object_id]=@TABLE_ID

    ORDER BY sys.columns.[column_id]

    --##############################################################################

    --used for formatting the rest of the constraints:

    --##############################################################################

    SELECT

    @STRINGLEN = MAX(LEN([name])) + 1

    FROM sys.objects

    --##############################################################################

    --PK/Unique Constraints and Indexes, using the 2005/08 INCLUDE syntax

    --##############################################################################

    DECLARE @Results TABLE (

    [schema_id] int,

    [schema_name] NVARCHAR(255),

    [object_id] int,

    [object_name] NVARCHAR(255),

    [index_id] int,

    [index_name] NVARCHAR(255),

    [Rows] int,

    [SizeMB] decimal(19,3),

    [IndexDepth] int,

    [type] int,

    [type_desc] NVARCHAR(30),

    [fill_factor] int,

    [is_unique] int,

    [is_primary_key] int ,

    [is_unique_constraint] int,

    [index_columns_key] NVARCHAR(max),

    [index_columns_include] NVARCHAR(max))

    INSERT INTO @Results

    SELECT

    sys.schemas.schema_id, sys.schemas.[name] AS schema_name,

    sys.objects.[object_id], sys.objects.[name] AS object_name,

    sys.indexes.index_id, ISNULL(sys.indexes.[name], '---') AS index_name,

    partitions.Rows, partitions.SizeMB, IndexProperty(sys.objects.[object_id], sys.indexes.[name], 'IndexDepth') AS IndexDepth,

    sys.indexes.type, sys.indexes.type_desc, sys.indexes.fill_factor,

    sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint,

    ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,

    ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include

    FROM

    sys.objects

    JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id

    JOIN sys.indexes ON sys.objects.[object_id]=sys.indexes.[object_id]

    JOIN (

    SELECT

    [object_id], index_id, SUM(row_count) AS Rows,

    CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB

    FROM sys.dm_db_partition_stats

    GROUP BY [object_id], index_id

    ) AS partitions ON sys.indexes.[object_id]=partitions.[object_id] AND sys.indexes.index_id=partitions.index_id

    CROSS APPLY (

    SELECT

    LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,

    LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include

    FROM

    (

    SELECT

    (

    SELECT sys.columns.[name] + ',' + ' '

    FROM

    sys.index_columns

    JOIN sys.columns ON

    sys.index_columns.column_id=sys.columns.column_id

    AND sys.index_columns.[object_id]=sys.columns.[object_id]

    WHERE

    sys.index_columns.is_included_column=0

    AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id

    ORDER BY key_ordinal

    FOR XML PATH('')

    ) AS index_columns_key,

    (

    SELECT sys.columns.[name] + ',' + ' '

    FROM

    sys.index_columns

    JOIN sys.columns ON

    sys.index_columns.column_id=sys.columns.column_id

    AND sys.index_columns.[object_id]=sys.columns.[object_id]

    WHERE

    sys.index_columns.is_included_column=1

    AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id

    ORDER BY index_column_id

    FOR XML PATH('')

    ) AS index_columns_include

    ) AS Index_Columns

    ) AS Index_Columns

    WHERE

    sys.schemas.[name] LIKE CASE WHEN @SCHEMANAME='' THEN sys.schemas.[name] ELSE @SCHEMANAME END

    AND sys.objects.[name] LIKE CASE WHEN @TBLNAME='' THEN sys.objects.[name] ELSE @TBLNAME END

    ORDER BY sys.schemas.[name], sys.objects.[name], sys.indexes.[name]

    --@Results table has both PK,s Uniques and indexes in thme...pull them out for adding to funal results:

    SET @CONSTRAINTSQLS = ''

    SET @INDEXSQLS = ''

    --##############################################################################

    --constriants

    --##############################################################################

    SELECT @CONSTRAINTSQLS = @CONSTRAINTSQLS +

    CASE

    WHEN is_primary_key = 1 or is_unique = 1

    THEN @vbCrLf

    + 'CONSTRAINT [' + index_name + '] '

    + SPACE(@STRINGLEN - LEN(index_name))

    + CASE WHEN is_primary_key = 1 THEN ' PRIMARY KEY ' ELSE CASE WHEN is_unique = 1 THEN ' UNIQUE ' ELSE '' END END

    + type_desc + CASE WHEN type_desc='NONCLUSTERED' THEN '' ELSE ' ' END

    + ' (' + index_columns_key + ')'

    + CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END

    + CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(NVARCHAR(30),fill_factor) ELSE '' END

    ELSE ''

    END + ','

    from @RESULTS

    where [type_desc] != 'HEAP'

    AND is_primary_key = 1 or is_unique = 1

    order by is_primary_key desc,is_unique desc

    --##############################################################################

    --indexes

    --##############################################################################

    SELECT @INDEXSQLS = @INDEXSQLS +

    CASE

    WHEN is_primary_key = 0 or is_unique = 0

    THEN @vbCrLf

    + 'CREATE INDEX [' + index_name + '] '

    + SPACE(@STRINGLEN - LEN(index_name))

    + ' ON [' + [object_name] + ']'

    + ' (' + index_columns_key + ')'

    + CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END

    + CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(NVARCHAR(30),fill_factor) ELSE '' END

    END

    from @RESULTS

    where [type_desc] != 'HEAP'

    AND is_primary_key = 0 AND is_unique = 0

    order by is_primary_key desc,is_unique desc

    IF @INDEXSQLS <> ''

    SET @INDEXSQLS = @vbCrLf + 'GO' + @vbCrLf + @INDEXSQLS

    --##############################################################################

    --CHECK Constraints

    --##############################################################################

    SET @CHECKCONSTSQLS = ''

    SELECT

    @CHECKCONSTSQLS = @CHECKCONSTSQLS

    + @vbCrLf

    + ISNULL('CONSTRAINT [' + sys.objects.[name] + '] '

    + SPACE(@STRINGLEN - LEN(sys.objects.[name]))

    + ' CHECK ' + ISNULL(sys.check_constraints.definition,'')

    + ',','')

    FROM sys.objects

    INNER JOIN sys.check_constraints ON sys.objects.[object_id] = sys.check_constraints.[object_id]

    WHERE sys.objects.type = 'C'

    AND sys.objects.parent_object_id = @TABLE_ID

    --##############################################################################

    --FOREIGN KEYS

    --##############################################################################

    SET @FKSQLS = '' ;

    SELECT

    @FKSQLS=@FKSQLS

    + @vbCrLf

    + 'CONSTRAINT [' + OBJECT_NAME(constid) +']'

    + SPACE(@STRINGLEN - LEN(OBJECT_NAME(constid) ))

    + ' FOREIGN KEY (' + COL_NAME(fkeyid,fkey)

    + ') REFERENCES ' + OBJECT_NAME(rkeyid)

    +'(' + COL_NAME(rkeyid,rkey) + '),'

    from sysforeignkeys

    WHERE fkeyid = @TABLE_ID

    --##############################################################################

    --RULES

    --##############################################################################

    SET @RULESCONSTSQLS = ''

    SELECT

    @RULESCONSTSQLS = @RULESCONSTSQLS

    + ISNULL(

    @vbCrLf

    + 'if not exists(SELECT [name] FROM sys.objects WHERE TYPE=''R'' AND schema_id = ' + convert(NVARCHAR(30),sys.objects.schema_id) + ' AND [name] = ''[' + object_name(sys.columns.[rule_object_id]) + ']'')' + @vbCrLf

    + sys.sql_modules.definition + @vbCrLf + 'GO' + @vbCrLf

    + 'EXEC sp_binderule [' + sys.objects.[name] + '], ''[' + OBJECT_NAME(sys.columns.[object_id]) + '].[' + sys.columns.[name] + ']''' + @vbCrLf + 'GO' ,'')

    from sys.columns

    inner join sys.objects

    on sys.objects.[object_id] = sys.columns.[object_id]

    inner join sys.sql_modules

    on sys.columns.[rule_object_id] = sys.sql_modules.[object_id]

    WHERE sys.columns.[rule_object_id] <> 0

    and sys.columns.[object_id] = @TABLE_ID

    --##############################################################################

    --TRIGGERS

    --##############################################################################

    SET @TRIGGERSTATEMENT = ''

    SELECT

    @TRIGGERSTATEMENT = @TRIGGERSTATEMENT + @vbCrLf + sys.sql_modules.[definition] + @vbCrLf + 'GO'

    FROM sys.sql_modules

    WHERE [object_id] IN(SELECT

    [object_id]

    FROM sys.objects

    WHERE type = 'TR'

    AND [parent_object_id] = @TABLE_ID)

    IF @TRIGGERSTATEMENT <> ''

    SET @TRIGGERSTATEMENT = @vbCrLf + 'GO' + @vbCrLf + @TRIGGERSTATEMENT

    --##############################################################################

    --NEW SECTION QUERY ALL EXTENDED PROPERTIES

    --##############################################################################

    SET @EXTENDEDPROPERTIES = ''

    SELECT @EXTENDEDPROPERTIES =

    @EXTENDEDPROPERTIES + @vbCrLf +

    'EXEC sys.sp_addextendedproperty

    @name = N''' + [name] + ''', @value = N''' + REPLACE(convert(NVARCHAR(max),[value]),'''','''''') + ''',

    @level0type = N''SCHEMA'', @level0name = [' + @SCHEMANAME + '],

    @level1type = N''TABLE'', @level1name = [' + @TBLNAME + '];'

    --SELECT objtype, objname, name, value

    FROM fn_listextendedproperty (NULL, 'schema', @SCHEMANAME, 'table', @TBLNAME, NULL, NULL);

    IF @EXTENDEDPROPERTIES <> ''

    SET @EXTENDEDPROPERTIES = @vbCrLf + 'GO' + @vbCrLf + @EXTENDEDPROPERTIES

    --##############################################################################

    --FINAL CLEANUP AND PRESENTATION

    --##############################################################################

    --at this point, there is a trailing comma, or it blank

    SELECT

    @FINALSQL = @FINALSQL

    + @CONSTRAINTSQLS

    + @CHECKCONSTSQLS

    + @FKSQLS

    --note that this trims the trailing comma from the end of the statements

    SET @FINALSQL = SUBSTRING(@FINALSQL,1,LEN(@FINALSQL) -1) ;

    SET @FINALSQL = @FINALSQL + ')' + @vbCrLf ;

    --Set @FINALSQL = ( @FINALSQL

    --+ @INDEXSQLS

    --+ @RULESCONSTSQLS

    --+ @TRIGGERSTATEMENT

    --+ @EXTENDEDPROPERTIES

    --)

    ----print @Result

    --note that this trims the trailing comma from the end of the statements

    SET @FINALSQL = SUBSTRING(@FINALSQL,1,LEN(@FINALSQL) -1) ;

    SET @Result = (@FINALSQL)

    SET @input = @FINALSQL

    + @INDEXSQLS

    + @RULESCONSTSQLS

    + @TRIGGERSTATEMENT

    + @EXTENDEDPROPERTIES

    --ten years worth of days from todays date:

    ;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1 UNION ALL

    SELECT 1 UNION ALL SELECT 1), -- 10 or 10E01 rows

    E02(N) AS (SELECT 1 FROM E01 a, E01 b), -- 100 or 10E02 rows

    E04(N) AS (SELECT 1 FROM E02 a, E02 b), -- 10,000 or 10E04 rows

    E08(N) AS (SELECT 1 FROM E04 a, E04 b), --100,000,000 or 10E08 rows

    --E16(N) AS (SELECT 1 FROM E08 a, E08 b), --10E16 or more rows than you'll EVER need,

    Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),

    ItemSplit(

    ItemOrder,

    Item

    ) as (

    SELECT N,

    SUBSTRING(@vbCrLf + @input + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @input + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))

    FROM Tally

    WHERE N < DATALENGTH(@vbCrLf + @input)

    --WHERE N < DATALENGTH(@vbCrLf + @input) -- REMOVED added @vbCrLf

    AND SUBSTRING(@vbCrLf + @input + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter

    )

    --create table dbo.DatabaseAuditDetail (Audit_ID int, ObjectName nvarchar(255), Row nvarchar(max))

    insert into ZKS_AUDIT_DB.dbo.DatabaseAuditDetail (Audit_ID, Row_ID, ObjectName, Row )

    select @Audit_ID, row_number() over (order by ItemOrder) as Row_ID, @TBLNAME, Item from ItemSplit

    END

    The trigger (without comments sorry no time)

    create trigger DatabaseAuditTrigger

    on database

    for ddl_database_level_events

    as

    set nocount on;

    declare @Cmd nvarchar(max)

    declare @EventData xml

    declare @Input nvarchar (255)

    declare @Object nvarchar(200)

    declare @PreviousVersion nvarchar(max)

    declare @Schema nvarchar(55)

    declare @Audit_ID int

    --Create audittabel (if not exists)

    IF NOT EXISTS (select 1 from ZKS_AUDIT_DB.sys.objects where name = 'DatabaseAudit')

    BEGIN

    create table [ZKS_AUDIT_DB].[dbo].[DatabaseAudit](

    [Audit_ID] int identity(1,1) primary key,

    [AuditDate] [datetime] not null,

    [LoginName] [sysname] null,

    [EventType] [sysname] null,

    [SchemaName] [sysname] null,

    [ObjectName] [sysname] not null,

    [TSQLCommand] [nvarchar](max) null,

    [PreviousVersion] [nvarchar](max) null,

    [XMLEventData] [xml] null

    ) ON [PRIMARY]

    END

    --Get the DDL that triggered the trigger

    set @EventData = eventdata()

    set @PreviousVersion = ''

    set @Schema = (select isnull

    ( @EventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'),

    @EventData.value('data(/EVENT_INSTANCE/DefaultSchema)[1]', 'sysname')

    )

    )

    set @Object = (select @EventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'))

    set @Input = @Schema + '.' + @Object

    set @cmd = (Select @EventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'varchar(max)'))

    --issue a rollback, so the previous situation is maintained

    ROLLBACK

    --instantiate SP_GETDDL to retrieve the previous DDL and write it to a variable @cmd

    exec Sp_Getddl @Input, @PreviousVersion output

    exec(@cmd)--execute the DDL that triggered this trigger again (although the rollback has occureD)

    set @Audit_ID = (select max(audit_id) from ZKS_AUDIT_DB.DBO.DatabaseAudit where ObjectName = @Object)

    --write the auditrecord

    UPDATE ZKS_AUDIT_DB.dbo.DatabaseAudit SET LoginName = @EventData.value('data(/EVENT_INSTANCE/LoginName)[1]', 'sysname'),

    EventType = @EventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'sysname'),

    TSQLCommand = @cmd,

    PreviousVersion = @PreviousVersion,

    XMLEventData = @EventData

    WHERE Audit_id = @Audit_ID

    --start a new transaction to disable the 3609 message

    begin tran

    The tables

    CREATE TABLE [dbo].[DatabaseAudit](

    [Audit_ID] [int] IDENTITY(1,1) NOT NULL,

    [AuditDate] [datetime] NOT NULL,

    [LoginName] [sysname] NULL,

    [EventType] [sysname] NULL,

    [SchemaName] [sysname] NULL,

    [ObjectName] [sysname] NOT NULL,

    [TSQLCommand] [nvarchar](max) NULL,

    [PreviousVersion] [nvarchar](max) NULL,

    [XMLEventData] [xml] NULL,

    PRIMARY KEY CLUSTERED

    (

    [Audit_ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    CREATE TABLE [dbo].[DatabaseAuditDetail](

    [Audit_ID] [int] NOT NULL,

    [Row_ID] [int] NOT NULL,

    [ObjectName] [nvarchar](255) NULL,

    [Row] [nvarchar](max) NULL,

    CONSTRAINT [PK_DatabaseAuditDetail] PRIMARY KEY CLUSTERED

    (

    [Audit_ID] ASC,

    [Row_ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

  • looks like mine is similar to yours; just different fields that i'm capturing.

    here's what i see as the differences:

    I'm using sp_GetDDL not sp_GetDDLa, which returns multiple rows. i am using v309 with no problems at all. if you can identify the errors you found, i'd appreciate it...you did mark it a s a system proc in master, right? my code is the same i wrote when my proc was version 306.

    i catch a few more columns of data.

    i'm using the audit table as a table in master, so i can simply deploy or not deploy my suite of triggers into a database, and the audit is not tracked inside the database i'm auditing.

    here's my code:

    use master

    CREATE TABLE [dbo].[DDLEVENTLOG] (

    [DATABASENAME] SYSNAME NOT NULL,

    [EVENTDATE] DATETIME NULL,

    [USERNAME] SYSNAME NULL DEFAULT (getdate()),

    [SYSTEMUSER] VARCHAR(128) NULL,

    [CURRENTUSER] VARCHAR(128) NULL,

    [ORIGINALUSER] VARCHAR(128) NULL,

    [HOSTNAME] VARCHAR(128) NULL,

    [APPLICATIONNAME] VARCHAR(128) NULL,

    [SCHEMANAME] SYSNAME NULL,

    [OBJECTNAME] SYSNAME NULL,

    [OBJECTTYPE] SYSNAME NULL,

    [EVENTTYPE] VARCHAR(128) NULL,

    [EVENTDATA] XML NULL,

    [COMMANDTEXT] VARCHAR(max) NULL,

    [OBJECTDEFINITION] VARCHAR(max) NULL)

    GO

    use SandBox

    GO

    ALTER TRIGGER [ReturnPREventData]

    ON DATABASE

    WITH EXECUTE AS 'dbo'

    FOR

    CREATE_PROCEDURE, DROP_PROCEDURE, ALTER_PROCEDURE,

    CREATE_FUNCTION, DROP_FUNCTION, ALTER_FUNCTION,

    CREATE_VIEW, DROP_VIEW, ALTER_VIEW,

    CREATE_TABLE, DROP_TABLE, ALTER_TABLE,

    CREATE_TRIGGER, DROP_TRIGGER, ALTER_TRIGGER

    AS

    BEGIN

    SET NOCOUNT ON

    declare @ObjectDef table(definition varchar(max))

    declare

    @eventData XML,

    @DATABASENAME SYSNAME,

    @EVENTDATE DATETIME,

    @USERNAME SYSNAME,

    @SYSTEMUSER VARCHAR(128),

    @CURRENTUSER VARCHAR(128),

    @ORIGINALUSER VARCHAR(128),

    @HOSTNAME VARCHAR(128),

    @APPLICATIONNAME VARCHAR(128),

    @SCHEMANAME SYSNAME,

    @OBJECTNAME SYSNAME,

    @OBJECTTYPE SYSNAME,

    @EVENTTYPE VARCHAR(128),

    @COMMANDTEXT VARCHAR(max),

    @NAMEFORDEFINITION VARCHAR(261)

    --Load Variables from the xml

    SET @eventData = eventdata()

    SELECT

    @DATABASENAME = db_name(),

    @EVENTDATE = GETDATE(),

    @USERNAME = @eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),

    @SYSTEMUSER = SUSER_SNAME(),

    @CURRENTUSER = CURRENT_USER,

    @ORIGINALUSER = ORIGINAL_LOGIN(),

    @HOSTNAME = HOST_NAME(),

    @APPLICATIONNAME = APP_NAME(),

    @SCHEMANAME = @eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),

    @OBJECTNAME = @eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),

    @OBJECTTYPE = @eventData.value('data(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME'),

    @COMMANDTEXT = @eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),

    @EVENTTYPE = @eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')

    --get the object definition

    SET @NAMEFORDEFINITION = '[' + @SCHEMANAME + '].[' + @OBJECTNAME + ']'

    INSERT INTO @ObjectDef(definition)

    EXEC sp_getDDL @NAMEFORDEFINITION

    --now save the audit info

    INSERT [master].[dbo].[DDLEventLog] (DATABASENAME,EVENTDATE,USERNAME,SYSTEMUSER,CURRENTUSER,ORIGINALUSER,HOSTNAME,APPLICATIONNAME,SCHEMANAME,OBJECTNAME,OBJECTTYPE,EVENTTYPE,EVENTDATA,COMMANDTEXT,OBJECTDEFINITION)

    SELECT

    @DATABASENAME,

    @EVENTDATE,

    @USERNAME,

    @SYSTEMUSER,

    @CURRENTUSER,

    @ORIGINALUSER,

    @HOSTNAME,

    @APPLICATIONNAME,

    @SCHEMANAME,

    @OBJECTNAME,

    @OBJECTTYPE,

    @EVENTTYPE,

    @eventData,

    @COMMANDTEXT,

    definition

    FROM @ObjectDef

    END --DB TRIGGER

    GO

    ENABLE TRIGGER [ReturnPREventData] ON DATABASE

    GO

    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!

  • The 309 version returned no result, it did not give an error, but it returned a blank string. I did not have the time to debug it, so I used the previous version, I thought it was v307.

    The main difference between your and mine trigger, is that mines execute a rollback, so you can get the DDL of the previous situation as well. Before I execute the rollback, I save the executed DDL command that triggered the trigger in a variable. The great thing about the rollback is that the values of variables are not rollbacked. So after the rollback I execute the DDL from this variable and log the DDL in the same table and same row.

    Now it is possible to save both the new and the old DDL in te same row.

    The detail table contains the new and old DDL and each line of code implicates a new record (actually this is your splitter functionality).

  • Hi

    About the collate section :

    + CASE

    WHEN collation IS NULL

    THEN ''

    ELSE ' COLLATE ' + sys.columns.collation

    END

    I'm using SQL Server 2008 and I have a bug trying to add this part :

    • the name of the column in sys.columns is "collation_name" not collation
    • this part should come before the [is_nullable] part

    so I tried to add it in the NVARCHAR and VARCHAR section right before the [is_nullable] part :

    CREATE PROCEDURE [dbo].[sp_GetDDL]

    @TBL VARCHAR(255)

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @TBLNAME VARCHAR(200),

    @SCHEMANAME VARCHAR(255),

    @STRINGLEN INT,

    @TABLE_ID INT,

    @FINALSQL VARCHAR(max),

    @CONSTRAINTSQLS VARCHAR(max),

    @CHECKCONSTSQLS VARCHAR(max),

    @RULESCONSTSQLS VARCHAR(max),

    @FKSQLS VARCHAR(max),

    @TRIGGERSTATEMENT VARCHAR(max),

    @EXTENDEDPROPERTIES VARCHAR(max),

    @INDEXSQLS VARCHAR(max),

    @vbCrLf CHAR(2)

    --##############################################################################

    -- INITIALIZE

    --##############################################################################

    --SET @TBL = '[DBO].[WHATEVER1]'

    --does the tablename contain a schema?

    SET @vbCrLf = CHAR(13) + CHAR(10)

    SELECT @SCHEMANAME = ISNULL(PARSENAME(@TBL,2),'dbo') ,

    @TBLNAME = PARSENAME(@TBL,1)

    SELECT

    @TABLE_ID = [object_id]

    FROM sys.objects

    WHERE [type] = 'U'

    AND [name] <> 'dtproperties'

    AND [name] = @TBLNAME

    AND [schema_id] = schema_id(@SCHEMANAME) ;

    --##############################################################################

    -- Check If TableName is Valid

    --##############################################################################

    IF ISNULL(@TABLE_ID,0) = 0

    BEGIN

    SET @FINALSQL = 'Table object [' + @SCHEMANAME + '].[' + UPPER(@TBLNAME) + '] does not exist in Database [' + db_name() + ']'

    SELECT @FINALSQL;

    RETURN 0

    END

    --##############################################################################

    -- Valid Table, Continue Processing

    --##############################################################################

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

    SELECT @TABLE_ID = OBJECT_ID(@TBLNAME)

    SELECT

    @STRINGLEN = MAX(LEN(sys.columns.[name])) + 1

    FROM sys.objects

    INNER JOIN sys.columns

    ON sys.objects.[object_id] = sys.columns.[object_id]

    AND sys.objects.[object_id] = @TABLE_ID;

    --##############################################################################

    --Get the columns, their definitions and defaults.

    --##############################################################################

    SELECT

    @FINALSQL = @FINALSQL

    + CASE

    WHEN sys.columns.[is_computed] = 1

    THEN @vbCrLf

    + '['

    + UPPER(sys.columns.[name])

    + '] '

    + SPACE(@STRINGLEN - LEN(sys.columns.[name]))

    + 'AS ' + ISNULL(CALC.definition,'')

    ELSE @vbCrLf

    + '['

    + UPPER(sys.columns.[name])

    + '] '

    + SPACE(@STRINGLEN - LEN(sys.columns.[name]))

    + UPPER(TYPE_NAME(sys.columns.[user_type_id]))

    + CASE

    --IE NUMERIC(10,2)

    WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('decimal','numeric')

    THEN '('

    + CONVERT(VARCHAR,sys.columns.[precision])

    + ','

    + CONVERT(VARCHAR,sys.columns.[scale])

    + ') '

    + SPACE(6 - LEN(CONVERT(VARCHAR,sys.columns.[precision])

    + ','

    + CONVERT(VARCHAR,sys.columns.[scale])))

    + SPACE(2)

    -- + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN COLUMNPROPERTY ( @TABLE_ID , sys.columns.[name] , 'IsIdentity' ) = 0

    THEN ' '

    ELSE ' IDENTITY('

    + CONVERT(VARCHAR,ISNULL(IDENT_SEED(@TBLNAME),1) )

    + ','

    + CONVERT(VARCHAR,ISNULL(IDENT_INCR(@TBLNAME),1) )

    + ')'

    END

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    --IE FLOAT(53)

    WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('float','real')

    THEN

    --addition: if 53, no need to specifically say (53), otherwise display it

    CASE

    WHEN sys.columns.[precision] = 53

    THEN SPACE(11 - LEN(CONVERT(VARCHAR,sys.columns.[precision])))

    + SPACE(7)

    + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

    + CONVERT(VARCHAR,sys.columns.[precision])

    + ') '

    + SPACE(6 - LEN(CONVERT(VARCHAR,sys.columns.[precision])))

    + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    --ie VARCHAR(40)

    WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('char','varchar')

    THEN CASE

    WHEN sys.columns.[max_length] = -1

    THEN '(max)'

    + SPACE(6 - LEN(CONVERT(VARCHAR,sys.columns.[max_length])))

    + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN sys.columns.collation_name IS NULL

    THEN ''

    ELSE ' COLLATE ' + sys.columns.collation_name

    END

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

    + CONVERT(VARCHAR,sys.columns.[max_length])

    + ') '

    + SPACE(6 - LEN(CONVERT(VARCHAR,sys.columns.[max_length])))

    + SPACE(7) + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN sys.columns.collation_name IS NULL

    THEN ''

    ELSE ' COLLATE ' + sys.columns.collation_name

    END

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    --ie NVARCHAR(40)

    WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('nchar','nvarchar')

    THEN CASE

    WHEN sys.columns.[max_length] = -1

    THEN '(max)'

    + SPACE(6 - LEN(CONVERT(VARCHAR,(sys.columns.[max_length]))))

    + SPACE(7)

    + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN sys.columns.collation_name IS NULL

    THEN ''

    ELSE ' COLLATE ' + sys.columns.collation_name

    END

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

    + CONVERT(VARCHAR,(sys.columns.[max_length]))

    + ') '

    + SPACE(6 - LEN(CONVERT(VARCHAR,(sys.columns.[max_length]))))

    + SPACE(7)

    + SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN sys.columns.collation_name IS NOT NULL

    THEN ''

    ELSE ' COLLATE ' + sys.columns.collation_name

    END

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    --ie datetime

    WHEN TYPE_NAME(sys.columns.[user_type_id]) IN ('datetime','money','text','image')

    THEN SPACE(18 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + ' '

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    --IE INT

    ELSE SPACE(16 - LEN(TYPE_NAME(sys.columns.[user_type_id])))

    + CASE

    WHEN COLUMNPROPERTY ( @TABLE_ID , sys.columns.[name] , 'IsIdentity' ) = 0

    THEN ' '

    ELSE ' IDENTITY('

    + CONVERT(VARCHAR,ISNULL(IDENT_SEED(@TBLNAME),1) )

    + ','

    + CONVERT(VARCHAR,ISNULL(IDENT_INCR(@TBLNAME),1) )

    + ')'

    END

    + SPACE(2)

    + CASE

    WHEN sys.columns.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    + CASE

    WHEN sys.columns.[default_object_id] = 0

    THEN ''

    --ELSE ' DEFAULT ' + ISNULL(def.[definition] ,'')

    --optional section in case NAMED default cosntraints are needed:

    ELSE @vbCrLf + 'CONSTRAINT [' + def.name + '] DEFAULT ' + ISNULL(def.[definition] ,'')

    --i thought it needed to be handled differently! NOT!

    END --CASE cdefault

    END --iscomputed

    + ','

    FROM sys.columns

    LEFT OUTER JOIN sys.default_constraints DEF

    on sys.columns.[default_object_id] = DEF.[object_id]

    LEFT OUTER JOIN sys.computed_columns CALC

    on sys.columns.[object_id] = CALC.[object_id]

    and sys.columns.[column_id] = CALC.[column_id]

    Where sys.columns.[object_id]=@TABLE_ID

    ORDER BY sys.columns.[column_id]

    --##############################################################################

    --used for formatting the rest of the constraints:

    --##############################################################################

    SELECT

    @STRINGLEN = MAX(LEN([name])) + 1

    FROM sys.objects

    --##############################################################################

    --PK/Unique Constraints and Indexes, using the 2005/08 INCLUDE syntax

    --##############################################################################

    DECLARE @Results TABLE (

    [schema_id] int,

    [schema_name] varchar(255),

    [object_id] int,

    [object_name] varchar(255),

    [index_id] int,

    [index_name] varchar(255),

    [Rows] int,

    [SizeMB] decimal(19,3),

    [IndexDepth] int,

    [type] int,

    [type_desc] varchar(30),

    [fill_factor] int,

    [is_unique] int,

    [is_primary_key] int ,

    [is_unique_constraint] int,

    [index_columns_key] varchar(max),

    [index_columns_include] varchar(max))

    INSERT INTO @Results

    SELECT

    sys.schemas.schema_id, sys.schemas.[name] AS schema_name,

    sys.objects.[object_id], sys.objects.[name] AS object_name,

    sys.indexes.index_id, ISNULL(sys.indexes.[name], '---') AS index_name,

    partitions.Rows, partitions.SizeMB, IndexProperty(sys.objects.[object_id], sys.indexes.[name], 'IndexDepth') AS IndexDepth,

    sys.indexes.type, sys.indexes.type_desc, sys.indexes.fill_factor,

    sys.indexes.is_unique, sys.indexes.is_primary_key, sys.indexes.is_unique_constraint,

    ISNULL(Index_Columns.index_columns_key, '---') AS index_columns_key,

    ISNULL(Index_Columns.index_columns_include, '---') AS index_columns_include

    FROM

    sys.objects

    JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id

    JOIN sys.indexes ON sys.objects.[object_id]=sys.indexes.[object_id]

    JOIN (

    SELECT

    [object_id], index_id, SUM(row_count) AS Rows,

    CONVERT(numeric(19,3), CONVERT(numeric(19,3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(numeric(19,3), 128)) AS SizeMB

    FROM sys.dm_db_partition_stats

    GROUP BY [object_id], index_id

    ) AS partitions ON sys.indexes.[object_id]=partitions.[object_id] AND sys.indexes.index_id=partitions.index_id

    CROSS APPLY (

    SELECT

    LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,

    LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include

    FROM

    (

    SELECT

    (

    SELECT sys.columns.[name] + ',' + ' '

    FROM

    sys.index_columns

    JOIN sys.columns ON

    sys.index_columns.column_id=sys.columns.column_id

    AND sys.index_columns.[object_id]=sys.columns.[object_id]

    WHERE

    sys.index_columns.is_included_column=0

    AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id

    ORDER BY key_ordinal

    FOR XML PATH('')

    ) AS index_columns_key,

    (

    SELECT sys.columns.[name] + ',' + ' '

    FROM

    sys.index_columns

    JOIN sys.columns ON

    sys.index_columns.column_id=sys.columns.column_id

    AND sys.index_columns.[object_id]=sys.columns.[object_id]

    WHERE

    sys.index_columns.is_included_column=1

    AND sys.indexes.[object_id]=sys.index_columns.[object_id] AND sys.indexes.index_id=sys.index_columns.index_id

    ORDER BY index_column_id

    FOR XML PATH('')

    ) AS index_columns_include

    ) AS Index_Columns

    ) AS Index_Columns

    WHERE

    sys.schemas.[name] LIKE CASE WHEN @SCHEMANAME='' THEN sys.schemas.[name] ELSE @SCHEMANAME END

    AND sys.objects.[name] LIKE CASE WHEN @TBLNAME='' THEN sys.objects.[name] ELSE @TBLNAME END

    ORDER BY sys.schemas.[name], sys.objects.[name], sys.indexes.[name]

    --@Results table has both PK,s Uniques and indexes in thme...pull them out for adding to funal results:

    SET @CONSTRAINTSQLS = ''

    SET @INDEXSQLS = ''

    --##############################################################################

    --constriants

    --##############################################################################

    SELECT @CONSTRAINTSQLS = @CONSTRAINTSQLS +

    CASE

    WHEN is_primary_key = 1 or is_unique = 1

    THEN @vbCrLf

    + 'CONSTRAINT [' + index_name + '] '

    + SPACE(@STRINGLEN - LEN(index_name))

    + CASE WHEN is_primary_key = 1 THEN ' PRIMARY KEY ' ELSE CASE WHEN is_unique = 1 THEN ' UNIQUE ' ELSE '' END END

    + type_desc + CASE WHEN type_desc='NONCLUSTERED' THEN '' ELSE ' ' END

    + ' (' + index_columns_key + ')'

    + CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END

    + CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor) ELSE '' END

    ELSE ''

    END + ','

    from @RESULTS

    where [type_desc] != 'HEAP'

    AND is_primary_key = 1 or is_unique = 1

    order by is_primary_key desc,is_unique desc

    --##############################################################################

    --indexes

    --##############################################################################

    SELECT @INDEXSQLS = @INDEXSQLS +

    CASE

    WHEN is_primary_key = 0 or is_unique = 0

    THEN @vbCrLf

    + 'CREATE INDEX [' + index_name + '] '

    + SPACE(@STRINGLEN - LEN(index_name))

    + ' ON [' + [object_name] + ']'

    + ' (' + index_columns_key + ')'

    + CASE WHEN index_columns_include <> '---' THEN ' INCLUDE (' + index_columns_include + ')' ELSE '' END

    + CASE WHEN fill_factor <> 0 THEN ' WITH FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor) ELSE '' END

    END

    from @RESULTS

    where [type_desc] != 'HEAP'

    AND is_primary_key = 0 AND is_unique = 0

    order by is_primary_key desc,is_unique desc

    IF @INDEXSQLS <> ''

    SET @INDEXSQLS = @vbCrLf + 'GO' + @vbCrLf + @INDEXSQLS

    --##############################################################################

    --CHECK Constraints

    --##############################################################################

    SET @CHECKCONSTSQLS = ''

    SELECT

    @CHECKCONSTSQLS = @CHECKCONSTSQLS

    + @vbCrLf

    + ISNULL('CONSTRAINT [' + sys.objects.[name] + '] '

    + SPACE(@STRINGLEN - LEN(sys.objects.[name]))

    + ' CHECK ' + ISNULL(sys.check_constraints.definition,'')

    + ',','')

    FROM sys.objects

    INNER JOIN sys.check_constraints ON sys.objects.[object_id] = sys.check_constraints.[object_id]

    WHERE sys.objects.type = 'C'

    AND sys.objects.parent_object_id = @TABLE_ID

    --##############################################################################

    --FOREIGN KEYS

    --##############################################################################

    SET @FKSQLS = '' ;

    SELECT

    @FKSQLS=@FKSQLS

    + @vbCrLf

    + 'CONSTRAINT [' + OBJECT_NAME(constid) +']'

    + SPACE(@STRINGLEN - LEN(OBJECT_NAME(constid) ))

    + ' FOREIGN KEY (' + COL_NAME(fkeyid,fkey)

    + ') REFERENCES ' + OBJECT_NAME(rkeyid)

    +'(' + COL_NAME(rkeyid,rkey) + '),'

    from sysforeignkeys

    WHERE fkeyid = @TABLE_ID

    --##############################################################################

    --RULES

    --##############################################################################

    SET @RULESCONSTSQLS = ''

    SELECT

    @RULESCONSTSQLS = @RULESCONSTSQLS

    + ISNULL(

    @vbCrLf

    + 'if not exists(SELECT [name] FROM sys.objects WHERE TYPE=''R'' AND schema_id = ' + convert(varchar(30),sys.objects.schema_id) + ' AND [name] = ''[' + object_name(sys.columns.[rule_object_id]) + ']'')' + @vbCrLf

    + sys.sql_modules.definition + @vbCrLf + 'GO' + @vbCrLf

    + 'EXEC sp_binderule [' + sys.objects.[name] + '], ''[' + OBJECT_NAME(sys.columns.[object_id]) + '].[' + sys.columns.[name] + ']''' + @vbCrLf + 'GO' ,'')

    from sys.columns

    inner join sys.objects

    on sys.objects.[object_id] = sys.columns.[object_id]

    inner join sys.sql_modules

    on sys.columns.[rule_object_id] = sys.sql_modules.[object_id]

    WHERE sys.columns.[rule_object_id] <> 0

    and sys.columns.[object_id] = @TABLE_ID

    --##############################################################################

    --TRIGGERS

    --##############################################################################

    SET @TRIGGERSTATEMENT = ''

    SELECT

    @TRIGGERSTATEMENT = @TRIGGERSTATEMENT + @vbCrLf + sys.sql_modules.[definition] + @vbCrLf + 'GO'

    FROM sys.sql_modules

    WHERE [object_id] IN(SELECT

    [object_id]

    FROM sys.objects

    WHERE type = 'TR'

    AND [parent_object_id] = @TABLE_ID)

    IF @TRIGGERSTATEMENT <> ''

    SET @TRIGGERSTATEMENT = @vbCrLf + 'GO' + @vbCrLf + @TRIGGERSTATEMENT

    --##############################################################################

    --NEW SECTION QUERY ALL EXTENDED PROPERTIES

    --##############################################################################

    SET @EXTENDEDPROPERTIES = ''

    SELECT @EXTENDEDPROPERTIES =

    @EXTENDEDPROPERTIES + @vbCrLf +

    'EXEC sys.sp_addextendedproperty

    @name = N''' + [name] + ''', @value = N''' + REPLACE(convert(varchar(max),[value]),'''','''''') + ''',

    @level0type = N''SCHEMA'', @level0name = [' + @SCHEMANAME + '],

    @level1type = N''TABLE'', @level1name = [' + @TBLNAME + '];'

    --SELECT objtype, objname, name, value

    FROM fn_listextendedproperty (NULL, 'schema', @SCHEMANAME, 'table', @TBLNAME, NULL, NULL);

    IF @EXTENDEDPROPERTIES <> ''

    SET @EXTENDEDPROPERTIES = @vbCrLf + 'GO' + @vbCrLf + @EXTENDEDPROPERTIES

    --##############################################################################

    --FINAL CLEANUP AND PRESENTATION

    --##############################################################################

    --at this point, there is a trailing comma, or it blank

    SELECT

    @FINALSQL = @FINALSQL

    + @CONSTRAINTSQLS

    + @CHECKCONSTSQLS

    + @FKSQLS

    --note that this trims the trailing comma from the end of the statements

    SET @FINALSQL = SUBSTRING(@FINALSQL,1,LEN(@FINALSQL) -1) ;

    SET @FINALSQL = @FINALSQL + ')' + @vbCrLf ;

    SELECT @FINALSQL

    + @INDEXSQLS

    + @RULESCONSTSQLS

    + @TRIGGERSTATEMENT

    + @EXTENDEDPROPERTIES

    END

    GO

    Great work Stormrage

    and thx

    MBS.

  • thank you very much for the feedback and correction;

    at one point the original script was pointing at the old compatibility tables like syscolumns; that badly named column is a legacy of that.

    i migrated it over to use the newer views, as that seemed the right thing to do;

    hope this is working out for 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!

  • Hello Lowell -

    First - thank you for always helping me out with my questions in the Newbies forum. I came across this work of yours today, and forgive the very neophyte-like question here, but how is this different than simply doing a CREATE to from Right-Clicking one of my existing tables? I know there has to be more to this than that - I have followed the thread here and see the revisions of your work, and everyones comments, but after having created the proc on my end, run it against one of my tables, I am find no real differences in your proc and my results from my previous description.

    I must be missing something. Please forgive the question to understand here, because I definitely mean no insult by asking it (I want to use this code to recreate my tables, but need to understand what it's going to do for me first).

    Thank you!

  • well, i've got two variations of this...one returns the definition as a single varchar(max), and the other splits the same varchar(max) on CrLf so it's multiple rows.

    one purpose is standardization...developers in my shop have different styles for the tables that they create, and that's no problem for me...but when it comes to the final deployment script, the well formatted well spaced version of the DDL this proc creates adds to a better presentation.

    another purpose of it is nothing more than SQL Server does not provide the ability to get this data via a TSQL statement natively.

    you have use the GUI, which in turn uses SMO. 7 plus years ago there was a post that asked if it could be done, and that got my mind working.

    in my case, I'm using it for a few different things....DDL auditing mostly...i can capture the old and new definitions of a table in an audit table.

    but i can also script a database's complete definition via TSQL, instead of an outside process; that lead me to make my own "schema checker", where i can compare the definition of one table to another....helps me a lot with remote client databases,

    another adaptation i have takes that table definition and makes the ORACLE database equivalent, based on my SQL table...that is a huge timesaver in my shop, where we support An app that connects to SQL or Oracle.

    A lot of it, at first, was to prove it could be done; just one more tool in the toolbox.

    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!

  • Also because mine is purtier!

    one of the main reasons i did this was formatting, and removing/pruning items that are optional in a table script, so the don't need to be defined explicitly if you are taking the defaults (like CREATE TABLE (...) ON PRIMARY

    compare these two:

    --From SSMS

    CREATE TABLE [dbo].[ICONINFO](

    [RESULTSID] [int] IDENTITY(1,1) NOT NULL,

    [FILEFULLNAME] [varchar](1024) NULL,

    [FILEPATH] [varchar](1024) NULL,

    [FILENAME] [varchar](100) NULL,

    [FILEEXTENSION] [varchar](10) NULL,

    [FILESIZE] [int] NULL,

    [CHECKSUM] [varchar](50) NULL,

    [CREATIONDATE] [datetime] NULL,

    [RESULTSDESCRIPTION] [varchar](100) NULL,

    CONSTRAINT [PK__IconInfo] PRIMARY KEY CLUSTERED

    (

    [RESULTSID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    --From sp_getddla

    CREATE TABLE [dbo].[ICONINFO] (

    [RESULTSID] INT IDENTITY(1,1) NOT NULL,

    [FILEFULLNAME] VARCHAR(1024) NULL,

    [FILEPATH] VARCHAR(1024) NULL,

    [FILENAME] VARCHAR(100) NULL,

    [FILEEXTENSION] VARCHAR(10) NULL,

    [FILESIZE] INT NULL,

    [CHECKSUM] VARCHAR(50) NULL,

    [CREATIONDATE] DATETIME NULL,

    [RESULTSDESCRIPTION] VARCHAR(100) NULL,

    CONSTRAINT [PK__IconInfo] PRIMARY KEY CLUSTERED (RESULTSID))

    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!

  • Hey Lowell,

    I'm sure you're not expecting a reply so long since you last updated, but here it is. This SP is absolutely fantastic - a work of art - but I seem to have found a small hiccup in it. I have some triggers which have comments in them, commented out not using /* and */, but the old -- instead. Since your result returns one potentially very very long row, everything beyond the first doube-dash is then a comment. Any ideas how we can get around this?

    Thanks in advance!:-D

    EDIT: I just realised your GetDDLa might just take care of exactly that, but the link to 309a seems to be dead 🙁

Viewing 15 posts - 16 through 30 (of 117 total)

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