Get DDL for any SQL 2005 table

  • nikus thank you for the feedback! i really appreciate it!

    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!

  • Hi

    I added another small improvment.

    The keyword "clustered" or "nonclustered" in the index creation statement.

    Without this all indexes are created as nonclustered indexes which is in the most cases correct.

    But theoreticly it is possible to create a clustered index which ist not a PK constraint.

    For such an index this keyword is necessary.

    Look for:

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

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

    and replace it with:

    SELECT @INDEXSQLS = @INDEXSQLS

    + CASE

    WHEN is_primary_key = 0 or is_unique = 0

    THEN @vbCrLf

    + 'CREATE ' + type_desc + ' 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

    Just the "CREATE INDEX" line was changed.

    Nikus

  • damn Nikus you've gotten two great improvements I've overlooked in as many days; my personal tunnel vision for the indexes was all scripted indexes were non clustered, so i never noticed differences. You've got a great attention for detail, awesome!

    Thank you very, very much for the feedback, and I've already updated the scripts with your improvements.

    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!

  • Hi Lowell

    I am using your procedure for an automatic update script (from develpoment --> productive server).

    During the test of the update script I found some differences which have lead to the improvements.

    So it was a matter of chance.

    I made an another improvement to add the filegroup of the table or indexes.

    This time I will just post the changed script to keep the post shorter.

    I added the declartion of the @DATASPACE_NAME variable.

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

    @ISSYSTEMOBJECT INT,

    @PROCNAME VARCHAR(256),

    @DATASPACE_NAME VARCHAR(max)

    The "dataspace_name" column was added on the end of the @Results table.

    The "ISNULL(DSP.name, '---') dataspace_name" line was added in the select part.

    The "INNER JOIN sys.data_spaces DSP ON DSP.data_space_id = IDX.data_space_id" line was added in the from part.

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

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

    [dataspace_name]VARCHAR(MAX))

    INSERT INTO @Results

    SELECT

    SCH.schema_id, SCH.[name] AS schema_name,

    OBJS.[object_id], OBJS.[name] AS object_name,

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

    partitions.Rows, partitions.SizeMB, IndexProperty(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,

    IDX.type, IDX.type_desc, IDX.fill_factor,

    IDX.is_unique, IDX.is_primary_key, IDX.is_unique_constraint,

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

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

    ISNULL(DSP.name, '---') dataspace_name

    FROM sys.objects OBJS

    INNER JOIN sys.schemas SCH ON OBJS.schema_id=SCH.schema_id

    INNER JOIN sys.indexes IDX ON OBJS.[object_id]=IDX.[object_id]

    INNER JOIN sys.data_spaces DSP ON DSP.data_space_id = IDX.data_space_id

    INNER 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 STATS

    GROUP BY [object_id], index_id

    ) AS partitions

    ON IDX.[object_id]=partitions.[object_id]

    AND IDX.index_id=partitions.index_id

    The last "case when dataspace_name ...." statement was added in the select part.

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

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

    + CASE

    WHEN dataspace_name <> '---'

    THEN ' ON [' + dataspace_name + ']'

    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

    The last "case when dataspace_name ...." statement was added in the select part.

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

    --indexes

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

    SELECT @INDEXSQLS = @INDEXSQLS

    + CASE

    WHEN is_primary_key = 0 or is_unique = 0

    THEN @vbCrLf

    + 'CREATE ' + type_desc + ' 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

    + CASE

    WHEN dataspace_name <> '---'

    THEN ' ON [' + dataspace_name + ']'

    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

    The "SELECT @DATASPACE_NAME .." statement was added

    and the last line was changed.

    The "WHERE type < 2" condition retuns either the filegroup of the clusterd index (if exists)

    which is the physical sort order of the table or the filegroup of the heap entry which is the filegroup of the table.

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

    --FINAL CLEANUP AND PRESENTATION

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

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

    SELECT @DATASPACE_NAME = dataspace_name FROM @Results

    WHERE type < 2

    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 + ') ON [' + @DATASPACE_NAME + ']' + @vbCrLf ;

    I am pretty far with the testing now,

    so I don't think that I will find anything else to improve.

    As I said earlier this procedure saved me a lot of work.

    Thanks again for sharing it.

    Nikus

  • i had avoided that part for portability issues;

    when i run scripts in my environment, we don't want to say what filegroup an object is on as far as the scripts go, because that can change from server to server, but i do have an alternate version i created for our clustered server that does exactly what you are suggesting, because on that server the scripts need to match the filegroups and such.

    I also have a 2008 version that does the filtered indexes, which were introduced with 2008; the version here is 2005, which had include columns, but not filtered indexes.

    I even have version that runs on a SQL server, but generates the DDL scripts compatible for use in an Oracle Environment(ie identity() columns become sequence and triggers, a lot more.

    I really appreciate your feedback on this; makes it really worthwhile for me to have put it out to the community.

    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!

  • Hi Lowell

    I found 3 other things which could be interesting for you.

    1) The column names in the index and constraint statements should be in squre brackets

    for indexes where the column name is an reserved word (like 'table').

    This can be easy done by inserting into the @result table

    like

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

    2) Since you have scripted the index option "FILLFACTOR"

    you may reconsider to script the "PAD_INDEX" too.

    "PAD_INDEX" status is stored in sys.indexes (column "is_padded")

    so it can be read out during the insert into the @result table.

    3) An "unique index" (clustered or nonclustered) is scripted as an "unique constraint"

    which is more o less a bug.

    But it is easy to fix.

    Orginal code (constraint part):

    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

    + CASE

    WHEN dataspace_name <> '---'

    THEN ' ON [' + dataspace_name + ']'

    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

    Corrected code (constraint part).

    "is_unique" was replaced by "is_unique_constraint":

    SELECT @CONSTRAINTSQLS = @CONSTRAINTSQLS

    + CASE

    WHEN is_primary_key = 1 or is_unique_constraint = 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

    + CASE

    WHEN dataspace_name <> '---'

    THEN ' ON [' + dataspace_name + ']'

    ELSE ''

    END

    ELSE ''

    END + ','

    FROM @RESULTS

    WHERE [type_desc] != 'HEAP'

    AND (is_primary_key = 1

    OR is_unique_constraint = 1)

    You yould go a step further and remove the outher "case when" statement which is not necessary

    this will make the code shorter and easier to read.

    SELECT @CONSTRAINTSQLS += @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

    + CASE

    WHEN dataspace_name <> '---'

    THEN ' ON [' + dataspace_name + ']'

    ELSE ''

    END

    + ','

    FROM @RESULTS

    WHERE [type_desc] != 'HEAP'

    AND (is_primary_key = 1

    OR is_unique_constraint = 1)

    Orginal code (index part):

    SELECT @INDEXSQLS = @INDEXSQLS

    + CASE

    WHEN is_primary_key = 0 or is_unique = 0

    THEN @vbCrLf

    + 'CREATE ' + type_desc + ' 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

    + CASE

    WHEN dataspace_name <> '---'

    THEN ' ON [' + dataspace_name + ']'

    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

    Corrected code (index part).

    "is_unique" was replaced by "is_unique_constraint"

    AND the "or" in the "where condition" and the "when condition" was replaced by "and"

    SELECT @INDEXSQLS = @INDEXSQLS

    + CASE

    WHEN is_primary_key = 0 and is_unique_constraint = 0

    THEN @vbCrLf

    + 'CREATE ' + type_desc + ' 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

    + CASE

    WHEN dataspace_name <> '---'

    THEN ' ON [' + dataspace_name + ']'

    ELSE ''

    END

    END

    FROM @RESULTS

    WHERE [type_desc] != 'HEAP'

    AND is_primary_key = 0

    AND is_unique_constraint = 0

    ORDER BY

    is_primary_key DESC,

    is_unique DESC

    In "index part" statement you can also remove the outer "case when" if you want.

    Here is a query to check if you have any unique indexes in your system.

    select *

    from sys.indexes i

    inner join sys.objects o

    on i.object_id = o.object_id

    where i.is_unique = 1

    and i.is_unique_constraint = 0

    and i.is_primary_key = 0

    and o.type = 'U'

    Here is some small code to test the 3 issues.

    Create the table and compare the management studio code

    with the code of your procedure.

    CREATE TABLE [dbo].[test](

    varchar(10) NOT NULL)

    CREATE UNIQUE NONCLUSTERED INDEX index_test ON [dbo].test

    (

    ASC

    )WITH (PAD_INDEX = ON, FILLFACTOR = 20) ON [INDEX]

    CREATE UNIQUE CLUSTERED INDEX index_test2 ON [dbo].test

    (

    ASC

    )WITH (PAD_INDEX = ON, FILLFACTOR = 20) ON [INDEX]

    Nikus

  • I made the following changes to v311a:

    1. Removed the UPPER() function from the table and column names because many people use case to separate words and there is no reason to force them all to uppercase. If a developer used uppercase it will be preserved, so this solution should work for everyone. If you have a reason to add it back, then please make it an option.

    2. Added a "New Name" parameter in case you want to script the creation of backup/history tables in addition to the original table. This also allows you to define the same name in a different database/server, in case you want to make a copy of a table in an archive location. The parameter is optional, so you can leave it off (and use the original name) or include it (either in-line or as a named parameter)...

    --Default will use the original name:

    EXEC sp_GetDDL 'dbo.Customers'

    --Adding an in-line parameter:

    EXEC sp_GetDDL 'dbo.Customers', 'CustomersBackup'

    --Adding a named parameter:

    EXEC sp_GetDDL 'dbo.Customers', @NewName='CustomersBackup'

    3. Added 10 optional parameters which let you turn off different parts of the code. This is important because if you are making a backup table you might not want the constraints, triggers or indexes. Note, I left the "collation" option turned off by default since you noted that you don't like it.

    ALTER PROCEDURE [dbo].[sp_GetDDL]

    @TBL VARCHAR(255),

    @NewName VARCHAR(255) = NULL,

    @IncludeIdentity BIT = 1,

    @IncludeUniqueKeys BIT = 1,

    @IncludeForeignKeys BIT = 1,

    @IncludeIndexes BIT = 1,

    @IncludeCheckConstraints BIT = 1,

    @IncludeRules BIT = 1,

    @IncludeTriggers BIT = 1,

    @IncludeExtendedProperties BIT = 1,

    @IncludeDefaults BIT = 1,

    @IncludeCollation BIT = 0

    Fyi, the optional parameters can be added individually if you just want to turn off one thing...

    EXEC sp_GetDDL 'dbo.Customers', @IncludeTriggers=0

    I did not fix it, but I wanted to mention that the horizontal spacing is not working properly. It creates a statement that is jagged (which I assume is the reason for it), and makes it take up more room on the screen so that it is easier to miss things which have been pushed to the far right. Personally I am not a fan of any unnecessary horizontal spacing (a single space is all I use) but if you want to include it you might want to get it working properly.

    Here is the complete modified code:

    ALTER PROCEDURE [dbo].[sp_GetDDL]

    @TBL VARCHAR(255),

    @NewName VARCHAR(255) = NULL,

    @IncludeIdentity BIT = 1,

    @IncludeUniqueKeys BIT = 1,

    @IncludeForeignKeys BIT = 1,

    @IncludeIndexes BIT = 1,

    @IncludeCheckConstraints BIT = 1,

    @IncludeRules BIT = 1,

    @IncludeTriggers BIT = 1,

    @IncludeExtendedProperties BIT = 1,

    @IncludeDefaults BIT = 1,

    @IncludeCollation BIT = 0

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

    @ISSYSTEMOBJECT INT,

    @PROCNAME VARCHAR(256),

    @input VARCHAR(max)

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

    -- INITIALIZE

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

    SET @input = ''

    --new code: determine whether this proc is marked as a system proc with sp_ms_marksystemobject,

    --which flips the is_ms_shipped bit in sys.objects

    SELECT @ISSYSTEMOBJECT = ISNULL(is_ms_shipped,0),@PROCNAME = ISNULL(name,'pr_GetDDL') FROM sys.objects WHERE object_id = @@PROCID

    IF @ISSYSTEMOBJECT IS NULL

    SELECT @ISSYSTEMOBJECT = ISNULL(is_ms_shipped,0),@PROCNAME = ISNULL(name,'pp_GetDDL') FROM master.sys.objects WHERE object_id = @@PROCID

    IF @ISSYSTEMOBJECT IS NULL

    SET @ISSYSTEMOBJECT = 0

    IF @PROCNAME IS NULL

    SET @PROCNAME = 'sp_GetDDL'

    --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 OBJS

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

    AND [name] <> 'dtproperties'

    AND [name] = @TBLNAME

    AND [schema_id] = schema_id(@SCHEMANAME) ;

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

    -- Check If TEMP TableName is Valid

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

    IF LEFT(@TBLNAME,1) = '#'

    BEGIN

    PRINT '--TEMP TABLE [' + @TBLNAME + '] FOUND'

    IF OBJECT_ID('tempdb..' + @TBLNAME) IS NOT NULL

    BEGIN

    PRINT '--GOIN TO TEMP PROCESSING'

    GOTO TEMPPROCESS

    END

    END

    ELSE

    BEGIN

    PRINT '--Non-Temp Table, [' + @TBLNAME + '] continue Processing'

    END

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

    -- Check If TableName is Valid

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

    IF ISNULL(@TABLE_ID,0) = 0

    BEGIN

    --V309 code: see if it is an object and not a table.

    SELECT

    @TABLE_ID = [object_id]

    FROM sys.objects OBJS

    --WHERE [type_desc] IN('SQL_STORED_PROCEDURE','VIEW','SQL_TRIGGER','AGGREGATE_FUNCTION','SQL_INLINE_TABLE_VALUED_FUNCTION','SQL_TABLE_VALUED_FUNCTION','SQL_SCALAR_FUNCTION')

    WHERE [type] IN ('P','V','TR','AF','IF','FN','TF')

    AND [name] <> 'dtproperties'

    AND [name] = @TBLNAME

    AND [schema_id] = schema_id(@SCHEMANAME) ;

    IF ISNULL(@TABLE_ID,0) <> 0

    BEGIN

    SELECT

    @FINALSQL = def.definition

    FROM sys.objects OBJS

    INNER JOIN sys.sql_modules def

    ON OBJS.object_id = def.object_id

    WHERE OBJS.[type] IN ('P','V','TR','AF','IF','FN','TF')

    AND OBJS.[name] <> 'dtproperties'

    AND OBJS.[name] = @TBLNAME

    AND OBJS.[schema_id] = schema_id(@SCHEMANAME) ;

    SET @input = @FINALSQL

    SELECT @input;

    RETURN 0

    END

    ELSE

    BEGIN

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

    + CASE

    WHEN @ISSYSTEMOBJECT = 0 THEN @vbCrLf + ' (also note that ' + @PROCNAME + ' is not marked as a system proc and cross db access to sys.tables will fail.)'

    ELSE ''

    END

    IF LEFT(@TBLNAME,1) = '#'

    SET @FINALSQL = @FINALSQL + ' OR in The tempdb database.'

    SELECT @FINALSQL AS Item;

    RETURN 0

    END

    END

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

    -- Valid Table, Continue Processing

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

    SELECT @FINALSQL = 'CREATE TABLE ' + COALESCE(@NewName, '[' + @SCHEMANAME + '].[' + @TBLNAME + ']') + ' ( '

    --removed invalid code here which potentially selected wrong table--thanks David Grifiths @SSC!

    SELECT

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

    FROM sys.objects OBJS

    INNER JOIN sys.columns COLS

    ON OBJS.[object_id] = COLS.[object_id]

    AND OBJS.[object_id] = @TABLE_ID;

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

    --Get the columns, their definitions and defaults.

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

    SELECT

    @FINALSQL = @FINALSQL

    + CASE

    WHEN COLS.[is_computed] = 1

    THEN @vbCrLf

    + '['

    + COLS.[name]

    + '] '

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

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

    + CASE

    WHEN CALC.is_persisted = 1

    THEN ' PERSISTED'

    ELSE ''

    END

    ELSE @vbCrLf

    + '['

    + COLS.[name]

    + '] '

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

    + UPPER(TYPE_NAME(COLS.[user_type_id]))

    + CASE

    --IE NUMERIC(10,2)

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

    THEN '('

    + CONVERT(VARCHAR,COLS.[precision])

    + ','

    + CONVERT(VARCHAR,COLS.[scale])

    + ') '

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

    + ','

    + CONVERT(VARCHAR,COLS.[scale])))

    + SPACE(7)

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

    + CASE WHEN @IncludeIdentity = 1 THEN CASE

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

    THEN ''

    ELSE ' IDENTITY('

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

    + ','

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

    + ')'

    END

    ELSE '' END

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    --IE FLOAT(53)

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

    THEN

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

    CASE

    WHEN COLS.[precision] = 53

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

    + SPACE(7)

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

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

    + CONVERT(VARCHAR,COLS.[precision])

    + ') '

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

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

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    --ie VARCHAR(40)

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

    THEN CASE

    WHEN COLS.[max_length] = -1

    THEN '(max)'

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

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

    + CASE WHEN @IncludeCollation = 1 THEN CASE

    WHEN COLS.collation_name IS NULL

    THEN ''

    ELSE ' COLLATE ' + COLS.collation_name

    END

    ELSE '' END

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

    + CONVERT(VARCHAR,COLS.[max_length])

    + ') '

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

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

    + CASE WHEN @IncludeCollation = 1 THEN CASE

    WHEN COLS.collation_name IS NULL

    THEN ''

    ELSE ' COLLATE ' + COLS.collation_name

    END

    ELSE '' END

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    --ie NVARCHAR(40)

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

    THEN CASE

    WHEN COLS.[max_length] = -1

    THEN '(max)'

    + SPACE(6 - LEN(CONVERT(VARCHAR,(COLS.[max_length] / 2))))

    + SPACE(7)

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

    + CASE WHEN @IncludeCollation = 1 THEN CASE

    WHEN COLS.collation_name IS NULL

    THEN ''

    ELSE ' COLLATE ' + COLS.collation_name

    END

    ELSE '' END

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

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

    + ') '

    + SPACE(6 - LEN(CONVERT(VARCHAR,(COLS.[max_length] / 2))))

    + SPACE(7)

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

    + CASE WHEN @IncludeCollation = 1 THEN CASE

    WHEN COLS.collation_name IS NULL

    THEN ''

    ELSE ' COLLATE ' + COLS.collation_name

    END

    ELSE '' END

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    --ie datetime

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

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

    + ' '

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    --IE VARBINARY(500)

    WHEN TYPE_NAME(COLS.[user_type_id]) = 'varbinary'

    THEN

    CASE

    WHEN COLS.[max_length] = -1

    THEN '(max)'

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

    + SPACE(7)

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

    + CASE WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

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

    + ') '

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

    + SPACE(7)

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

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    --IE INT

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

    + CASE WHEN @IncludeIdentity = 1 THEN CASE

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

    THEN ' '

    ELSE ' IDENTITY('

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

    + ','

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

    + ')'

    END

    ELSE '' END

    + SPACE(2)

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    + CASE WHEN @IncludeDefaults = 1 THEN CASE

    WHEN COLS.[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

    ELSE '' END

    END --iscomputed

    + ','

    FROM sys.columns COLS

    LEFT OUTER JOIN sys.default_constraints DEF

    ON COLS.[default_object_id] = DEF.[object_id]

    LEFT OUTER JOIN sys.computed_columns CALC

    ON COLS.[object_id] = CALC.[object_id]

    AND COLS.[column_id] = CALC.[column_id]

    WHERE COLS.[object_id]=@TABLE_ID

    ORDER BY COLS.[column_id]

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

    --used for formatting the rest of the constraints:

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

    SELECT

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

    FROM sys.objects OBJS

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

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

    SCH.schema_id, SCH.[name] AS schema_name,

    OBJS.[object_id], OBJS.[name] AS object_name,

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

    partitions.Rows, partitions.SizeMB, IndexProperty(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,

    IDX.type, IDX.type_desc, IDX.fill_factor,

    IDX.is_unique, IDX.is_primary_key, IDX.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 OBJS

    INNER JOIN sys.schemas SCH ON OBJS.schema_id=SCH.schema_id

    INNER JOIN sys.indexes IDX ON OBJS.[object_id]=IDX.[object_id]

    INNER 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 STATS

    GROUP BY [object_id], index_id

    ) AS partitions

    ON IDX.[object_id]=partitions.[object_id]

    AND IDX.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 COLS.[name] + case when IXCOLS.is_descending_key = 0 then ' asc' else ' desc' end + ',' + ' '

    FROM sys.index_columns IXCOLS

    INNER JOIN sys.columns COLS

    ON IXCOLS.column_id = COLS.column_id

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

    WHERE IXCOLS.is_included_column = 0

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

    AND IDX.index_id = IXCOLS.index_id

    ORDER BY key_ordinal

    FOR XML PATH('')

    ) AS index_columns_key,

    (

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

    FROM sys.index_columns IXCOLS

    INNER JOIN sys.columns COLS

    ON IXCOLS.column_id = COLS.column_id

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

    WHERE IXCOLS.is_included_column = 1

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

    AND IDX.index_id = IXCOLS.index_id

    ORDER BY index_column_id

    FOR XML PATH('')

    ) AS index_columns_include

    ) AS Index_Columns

    ) AS Index_Columns

    WHERE SCH.[name] LIKE CASE

    WHEN @SCHEMANAME = ''

    THEN SCH.[name]

    ELSE @SCHEMANAME

    END

    AND OBJS.[name] LIKE CASE

    WHEN @TBLNAME = ''

    THEN OBJS.[name]

    ELSE @TBLNAME

    END

    ORDER BY

    SCH.[name],

    OBJS.[name],

    IDX.[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

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

    IF @IncludeUniqueKeys = 1 BEGIN

    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

    END --@IncludeUniqueKeys

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

    --indexes

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

    IF @IncludeIndexes = 1 BEGIN

    SELECT @INDEXSQLS = @INDEXSQLS

    + CASE

    WHEN is_primary_key = 0 or is_unique = 0

    THEN @vbCrLf

    + 'CREATE ' + type_desc + ' 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

    END --@IncludeIndexes

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

    --CHECK Constraints

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

    SET @CHECKCONSTSQLS = ''

    IF @IncludeCheckConstraints = 1 BEGIN

    SELECT

    @CHECKCONSTSQLS = @CHECKCONSTSQLS

    + @vbCrLf

    + ISNULL('CONSTRAINT [' + OBJS.[name] + '] '

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

    + ' CHECK ' + ISNULL(CHECKS.definition,'')

    + ',','')

    FROM sys.objects OBJS

    INNER JOIN sys.check_constraints CHECKS ON OBJS.[object_id] = CHECKS.[object_id]

    WHERE OBJS.type = 'C'

    AND OBJS.parent_object_id = @TABLE_ID

    END --@IncludeCheckConstraints

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

    --FOREIGN KEYS

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

    SET @FKSQLS = '' ;

    IF @IncludeForeignKeys = 1 BEGIN

    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 FKEYS

    WHERE fkeyid = @TABLE_ID

    END --@IncludeForeignKeys

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

    --RULES

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

    SET @RULESCONSTSQLS = ''

    IF @IncludeRules = 1 BEGIN

    SELECT

    @RULESCONSTSQLS = @RULESCONSTSQLS

    + ISNULL(

    @vbCrLf

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

    + MODS.definition + @vbCrLf + 'GO' + @vbCrLf

    + 'EXEC sp_binderule [' + OBJS.[name] + '], ''[' + OBJECT_NAME(COLS.[object_id]) + '].[' + COLS.[name] + ']''' + @vbCrLf + 'GO' ,'')

    FROM sys.columns COLS

    INNER JOIN sys.objects OBJS

    ON OBJS.[object_id] = COLS.[object_id]

    INNER JOIN sys.sql_modules MODS

    ON COLS.[rule_object_id] = MODS.[object_id]

    WHERE COLS.[rule_object_id] <> 0

    AND COLS.[object_id] = @TABLE_ID

    END --@IncludeRules

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

    --TRIGGERS

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

    SET @TRIGGERSTATEMENT = ''

    IF @IncludeTriggers = 1 BEGIN

    SELECT

    @TRIGGERSTATEMENT = @TRIGGERSTATEMENT + @vbCrLf + MODS.[definition] + @vbCrLf + 'GO'

    FROM sys.sql_modules MODS

    WHERE [object_id] IN(SELECT

    [object_id]

    FROM sys.objects OBJS

    WHERE type = 'TR'

    AND [parent_object_id] = @TABLE_ID)

    IF @TRIGGERSTATEMENT <> ''

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

    END --@IncludeTriggers

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

    --NEW SECTION QUERY ALL EXTENDED PROPERTIES

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

    SET @EXTENDEDPROPERTIES = ''

    IF @IncludeExtendedProperties = 1 BEGIN

    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

    END --@IncludeExtendedProperties

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

    --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 @input = @vbCrLf

    + @FINALSQL

    + @INDEXSQLS

    + @RULESCONSTSQLS

    + @TRIGGERSTATEMENT

    + @EXTENDEDPROPERTIES

    SELECT @input

    RETURN;

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

    -- END Normal Table Processing

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

    --simple, primitive version to get the results of a TEMP table from the TEMP db.

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

    -- NEW Temp Table Logic

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

    TEMPPROCESS:

    SELECT @TABLE_ID = OBJECT_ID('tempdb..' + @TBLNAME)

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

    -- Valid Table, Continue Processing

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

    SELECT @FINALSQL = 'CREATE TABLE ' + COALESCE(@NewName, '[' + @SCHEMANAME + '].[' + @TBLNAME + ']') + ' ( '

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

    SELECT

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

    FROM tempdb.sys.objects OBJS

    INNER JOIN tempdb.sys.columns COLS

    ON OBJS.[object_id] = COLS.[object_id]

    AND OBJS.[object_id] = @TABLE_ID;

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

    --Get the columns, their definitions and defaults.

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

    SELECT

    @FINALSQL = @FINALSQL

    + CASE

    WHEN COLS.[is_computed] = 1

    THEN @vbCrLf

    + '['

    + COLS.[name]

    + '] '

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

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

    + CASE

    WHEN CALC.is_persisted = 1

    THEN ' PERSISTED'

    ELSE ''

    END

    ELSE @vbCrLf

    + '['

    + COLS.[name]

    + '] '

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

    + UPPER(TYPE_NAME(COLS.[user_type_id]))

    + CASE

    --IE NUMERIC(10,2)

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

    THEN '('

    + CONVERT(VARCHAR,COLS.[precision])

    + ','

    + CONVERT(VARCHAR,COLS.[scale])

    + ') '

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

    + ','

    + CONVERT(VARCHAR,COLS.[scale])))

    + SPACE(7)

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

    + CASE WHEN @IncludeIdentity = 1 THEN CASE

    WHEN COLS.is_identity = 1

    THEN ' IDENTITY(1,1)'

    ELSE ''

    ----WHEN COLUMNPROPERTY ( @TABLE_ID , COLS.[name] , 'IsIdentity' ) = 1

    ----THEN ' IDENTITY('

    ---- + CONVERT(VARCHAR,ISNULL(IDENT_SEED('tempdb..' + @TBLNAME),1) )

    ---- + ','

    ---- + CONVERT(VARCHAR,ISNULL(IDENT_INCR('tempdb..' + @TBLNAME),1) )

    ---- + ')'

    ----ELSE ''

    END

    ELSE '' END

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    --IE FLOAT(53)

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

    THEN

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

    CASE

    WHEN COLS.[precision] = 53

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

    + SPACE(7)

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

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

    + CONVERT(VARCHAR,COLS.[precision])

    + ') '

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

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

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    --ie VARCHAR(40)

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

    THEN CASE

    WHEN COLS.[max_length] = -1

    THEN '(max)'

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

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

    + CASE WHEN @IncludeCollation = 1 THEN CASE

    WHEN COLS.collation_name IS NULL

    THEN ''

    ELSE ' COLLATE ' + COLS.collation_name

    END

    ELSE '' END

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

    + CONVERT(VARCHAR,COLS.[max_length])

    + ') '

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

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

    + CASE WHEN @IncludeCollation = 1 THEN CASE

    WHEN COLS.collation_name IS NULL

    THEN ''

    ELSE ' COLLATE ' + COLS.collation_name

    END

    ELSE '' END

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    --ie NVARCHAR(40)

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

    THEN CASE

    WHEN COLS.[max_length] = -1

    THEN '(max)'

    + SPACE(6 - LEN(CONVERT(VARCHAR,(COLS.[max_length] / 2))))

    + SPACE(7)

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

    + CASE WHEN @IncludeCollation = 1 THEN CASE

    WHEN COLS.collation_name IS NULL

    THEN ''

    ELSE ' COLLATE ' + COLS.collation_name

    END

    ELSE '' END

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

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

    + ') '

    + SPACE(6 - LEN(CONVERT(VARCHAR,(COLS.[max_length] / 2))))

    + SPACE(7)

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

    + CASE WHEN @IncludeCollation = 1 THEN CASE

    WHEN COLS.collation_name IS NULL

    THEN ''

    ELSE ' COLLATE ' + COLS.collation_name

    END

    ELSE '' END

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    --ie datetime

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

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

    + ' '

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    --IE VARBINARY(500)

    WHEN TYPE_NAME(COLS.[user_type_id]) = 'varbinary'

    THEN

    CASE

    WHEN COLS.[max_length] = -1

    THEN '(max)'

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

    + SPACE(7)

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

    + CASE WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    ELSE '('

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

    + ') '

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

    + SPACE(7)

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

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    --IE INT

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

    + CASE WHEN @IncludeIdentity = 1 THEN CASE

    WHEN COLS.is_identity = 1

    THEN ' IDENTITY(1,1)'

    ELSE ' '

    ----WHEN COLUMNPROPERTY ( @TABLE_ID , COLS.[name] , 'IsIdentity' ) = 1

    ----THEN ' IDENTITY('

    ---- + CONVERT(VARCHAR,ISNULL(IDENT_SEED('tempdb..' + @TBLNAME),1) )

    ---- + ','

    ---- + CONVERT(VARCHAR,ISNULL(IDENT_INCR('tempdb..' + @TBLNAME),1) )

    ---- + ')'

    ----ELSE ' '

    END

    ELSE '' END

    + SPACE(2)

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    END

    + CASE WHEN @IncludeDefaults = 1 THEN CASE

    WHEN COLS.[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

    ELSE '' END

    END --iscomputed

    + ','

    FROM tempdb.sys.columns COLS

    LEFT OUTER JOIN tempdb.sys.default_constraints DEF

    ON COLS.[default_object_id] = DEF.[object_id]

    LEFT OUTER JOIN tempdb.sys.computed_columns CALC

    ON COLS.[object_id] = CALC.[object_id]

    AND COLS.[column_id] = CALC.[column_id]

    WHERE COLS.[object_id]=@TABLE_ID

    ORDER BY COLS.[column_id]

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

    --used for formatting the rest of the constraints:

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

    SELECT

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

    FROM tempdb.sys.objects OBJS

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

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

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

    DECLARE @Results2 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 @Results2

    SELECT

    SCH.schema_id, SCH.[name] AS schema_name,

    OBJS.[object_id], OBJS.[name] AS object_name,

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

    partitions.Rows, partitions.SizeMB, IndexProperty(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,

    IDX.type, IDX.type_desc, IDX.fill_factor,

    IDX.is_unique, IDX.is_primary_key, IDX.is_unique_constraint,

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

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

    FROM tempdb.sys.objects OBJS

    INNER JOIN tempdb.sys.schemas SCH ON OBJS.schema_id=SCH.schema_id

    INNER JOIN tempdb.sys.indexes IDX ON OBJS.[object_id]=IDX.[object_id]

    INNER 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 tempdb.sys.dm_db_partition_stats STATS

    GROUP BY [object_id], index_id

    ) AS partitions

    ON IDX.[object_id]=partitions.[object_id]

    AND IDX.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 COLS.[name] + case when IXCOLS.is_descending_key = 0 then ' asc' else ' desc' end + ',' + ' '

    FROM tempdb.sys.index_columns IXCOLS

    INNER JOIN tempdb.sys.columns COLS

    ON IXCOLS.column_id = COLS.column_id

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

    WHERE IXCOLS.is_included_column = 0

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

    AND IDX.index_id = IXCOLS.index_id

    ORDER BY key_ordinal

    FOR XML PATH('')

    ) AS index_columns_key,

    (

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

    FROM tempdb.sys.index_columns IXCOLS

    INNER JOIN tempdb.sys.columns COLS

    ON IXCOLS.column_id = COLS.column_id

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

    WHERE IXCOLS.is_included_column = 1

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

    AND IDX.index_id = IXCOLS.index_id

    ORDER BY index_column_id

    FOR XML PATH('')

    ) AS index_columns_include

    ) AS Index_Columns

    ) AS Index_Columns

    WHERE SCH.[name] LIKE CASE

    WHEN @SCHEMANAME = ''

    THEN SCH.[name]

    ELSE @SCHEMANAME

    END

    AND OBJS.[name] LIKE CASE

    WHEN @TBLNAME = ''

    THEN OBJS.[name]

    ELSE @TBLNAME

    END

    ORDER BY

    SCH.[name],

    OBJS.[name],

    IDX.[name]

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

    SET @CONSTRAINTSQLS = ''

    SET @INDEXSQLS = ''

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

    --constriants

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

    IF @IncludeUniqueKeys = 1 BEGIN

    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 @Results2

    WHERE [type_desc] != 'HEAP'

    AND is_primary_key = 1

    OR is_unique = 1

    ORDER BY

    is_primary_key DESC,

    is_unique DESC

    END --@IncludeUniqueKeys

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

    --indexes

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

    IF @IncludeIndexes = 1 BEGIN

    SELECT @INDEXSQLS = @INDEXSQLS

    + CASE

    WHEN is_primary_key = 0 or is_unique = 0

    THEN @vbCrLf

    + 'CREATE ' + type_desc + ' 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 @Results2

    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

    END --@IncludeIndexes

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

    --CHECK Constraints

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

    SET @CHECKCONSTSQLS = ''

    IF @IncludeCheckConstraints = 1 BEGIN

    SELECT

    @CHECKCONSTSQLS = @CHECKCONSTSQLS

    + @vbCrLf

    + ISNULL('CONSTRAINT [' + OBJS.[name] + '] '

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

    + ' CHECK ' + ISNULL(CHECKS.definition,'')

    + ',','')

    FROM tempdb.sys.objects OBJS

    INNER JOIN tempdb.sys.check_constraints CHECKS ON OBJS.[object_id] = CHECKS.[object_id]

    WHERE OBJS.type = 'C'

    AND OBJS.parent_object_id = @TABLE_ID

    END --@IncludeCheckConstraints

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

    --FOREIGN KEYS

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

    SET @FKSQLS = '' ;

    IF @IncludeForeignKeys = 1 BEGIN

    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 FKEYS

    WHERE fkeyid = @TABLE_ID

    END --@IncludeForeignKeys

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

    --RULES

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

    SET @RULESCONSTSQLS = ''

    IF @IncludeRules = 1 BEGIN

    SELECT

    @RULESCONSTSQLS = @RULESCONSTSQLS

    + ISNULL(

    @vbCrLf

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

    + MODS.definition + @vbCrLf + 'GO' + @vbCrLf

    + 'EXEC sp_binderule [' + OBJS.[name] + '], ''[' + OBJECT_NAME(COLS.[object_id]) + '].[' + COLS.[name] + ']''' + @vbCrLf + 'GO' ,'')

    FROM tempdb.sys.columns COLS

    INNER JOIN tempdb.sys.objects OBJS

    ON OBJS.[object_id] = COLS.[object_id]

    INNER JOIN tempdb.sys.sql_modules MODS

    ON COLS.[rule_object_id] = MODS.[object_id]

    WHERE COLS.[rule_object_id] <> 0

    AND COLS.[object_id] = @TABLE_ID

    END --@IncludeRules

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

    --TRIGGERS

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

    SET @TRIGGERSTATEMENT = ''

    IF @IncludeTriggers = 1 BEGIN

    SELECT

    @TRIGGERSTATEMENT = @TRIGGERSTATEMENT + @vbCrLf + MODS.[definition] + @vbCrLf + 'GO'

    FROM tempdb.sys.sql_modules MODS

    WHERE [object_id] IN(SELECT

    [object_id]

    FROM tempdb.sys.objects OBJS

    WHERE type = 'TR'

    AND [parent_object_id] = @TABLE_ID)

    IF @TRIGGERSTATEMENT <> ''

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

    END --@IncludeTriggers

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

    --NEW SECTION QUERY ALL EXTENDED PROPERTIES

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

    SET @EXTENDEDPROPERTIES = ''

    IF @IncludeExtendedProperties = 1 BEGIN

    SELECT @EXTENDEDPROPERTIES =

    @EXTENDEDPROPERTIES + @vbCrLf +

    'EXEC tempdb.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

    END --@IncludeExtendedProperties

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

    --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 @input = @vbCrLf

    + @FINALSQL

    + @INDEXSQLS

    + @RULESCONSTSQLS

    + @TRIGGERSTATEMENT

    + @EXTENDEDPROPERTIES

    SELECT @input;

    RETURN;

    END --PROC

    Ben

  • Hi,

    I am having an issue with the getting the DDL for tables with nonclustered indexes having the addition columns as constraints.

    The script marks this in the comment sections that the syntax is only compatible with SQL Server 2005/08 editions.

    "

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

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

    "

    Are there any modifications happening in place to convert the index logic to match the 2012 standard?

  • Great job !

    Just what I need.

    I'm going to try and if I'll have suggesionns.. I' ll post them.

    Thanks All

    Sal

  • When the index is scripted, it doesn't include the schema, i.e. line 566:

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

    Should be

    ' ON [' + [schema_name] + '].[' + [object_name] + ']'

    And the same further down.

    Great script. Thanks

  • This is helpful, very nice. Thanks for sharing.

    It would appear this does not account for Extended Properties at the column level. Added this code to catch the extended properties for columns:

    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 + '],

    @level2type = N''COLUMN'', @level2name = [' + [objname] + '];'

    --SELECT objtype, objname, name, value

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

    Also there is no sorting on included columns in indexes.

    So I changed the line in the 2nd sub select in two queries

    from

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

    to

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

  • Hi All,

    Could you please share latest and updated script.

    Many Thanks in advance!!

    -Ganesh Choudhari

  • OK for the folks who have bothered to follow this thread, a minor update, with a bunch of changes that i added myself,as well as some that were also contributed through the fine people here, who helped make this even better.

    --V314 03/30/2015

    -- did i mention this scripts out temp tables too? sp_GetDDL #tmp

    -- scripts a LOT more objects... a better replacement for sp_helptext i think.

    • table,
    • #temptable
    • procedure,
    • function,
    • view
    • trigger
    • synonym (new!)

    -- added ability to script synonyms

    -- moved logic for REAL datatype to fix error when scripting real columns

    -- added OmaCoders suggestion to script column extended properties as well.

    -- added matt_slack suggestion to script schemaname as part of index portion of script.

    -- minor script cleanup to use QUOTENAME insead of concatenating square brackets.

    -- changed compatibility to 2008 and above only, now filtered idnexes with WHERE statmeents script correctly

    -- foreign key tables and columns in script now quotenamed to accoutn for spaces in names; previously an error for Applciation ID instead of [Application ID]

    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!

  • it does not work on SQL 2008 R2. Is there any other working version available ?

  • I downloaded the script from http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt, it does not work on SQL2008 R2. It does not print all the columns of the below table.

    CREATE TABLE dbo.Test1 ( id int NOT NULL, name VARCHAR(100), PRIMARY KEY (id) );

    Is there any other version available ?

Viewing 15 posts - 61 through 75 (of 124 total)

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