Get DDL for any SQL 2005 table

  • The master, NewDB, and OldDB are all SQL_Latin1_General_CP1_CI_AS.

    I restored my template database (this is the one that we restore to setup new sites) and the header said it was version 661 which is SQL 2008 (NOT R2). I am now running SQL 2008 R2 first release, but I also tested on SQL 2012. This restored template database had the issue of only the last column being returned for existing tables. New tables worked. And then after I added the (SELECT ) to sp_getddl returned all columns.

    ELSE (SELECT ' CONSTRAINT ' + quotename(def.name) + ' DEFAULT ' + ISNULL(def.[definition] ,''))

  • I find a bug:

    Because the tsql statement SPACE(negative_number) return NULL, if is defined a user type with a name bigger than 16 char the sp_GetDDL return NULL

    To solve i replaced all occourrence of SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id]))) with isnull(SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id]))),'')

    alternatively if you will preserve the allignement, declare a variable @TYPELEN modify the code from: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;toSELECT

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

    @TYPELEN = MAX(LEN(typ.[name])) + 1

    FROM sys.objects OBJS

    INNER JOIN sys.columns COLS

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

    AND OBJS.[object_id] = @TABLE_ID

    INNER JOIN sys.types typ

    ON COLS.user_type_id=typ.user_type_id

    and replace all occourrence of

    SPACE(16 - LEN(TYPE_NAME(COLS.[user_type_id]))) withSPACE(@TYPELEN - LEN(TYPE_NAME(COLS.[user_type_id])))

  • ahh, so you are using some custom data types you've created, right?

    i had assumed the longest string datatype was "uniqueidentifier", and that's where the 16 came from.

    good catch, i'll wiggle the code to discover the longest name possible.

    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!

  • Yes i have some custom data type like CREATE TYPE validita_inizio_estesa FROM datetime NOT NULL

    CREATE TYPE validita_fine_estesa FROM datetime NULL

    .

    I also verified that is not coreccted handled the system data type Binary so you need replace WHEN TYPE_NAME(COLS.[user_type_id]) = 'varbinary'withWHEN TYPE_NAME(COLS.[user_type_id]) IN ('binary','varbinary')

    Maurizio

  • I modified the "--Get the columns, their definitions and defaults" in order solve the problem of custom data type having name > 16 char, to include 'binary' data type and also the new sql2008 data type 'datetime2', 'datetimeoffset' and 'time' , doing that i changed a little bit the logic.

    definitionDECLARE @DbCollationVARCHAR(255) = convert(varchar(255) ,databasepropertyex(db_name(),'collation')),

    DECLARE @TYPELENINT

    from and join to be modified for use in temp table --removed invalid code here which potentially selected wrong table--thanks David Grifiths @SSC!

    SELECT

    @STRINGLEN = MAX(LEN(QUOTENAME(COLS.[name]))) + 1, --modified

    @TYPELEN = MAX(LEN(typ.[name])) + 1 -- modified

    FROM sys.objects OBJS

    INNER JOIN sys.columns COLS

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

    AND OBJS.[object_id] = @TABLE_ID

    INNER JOIN sys.types typ

    ON COLS.user_type_id=typ.user_type_id

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

    --Get the columns, their definitions and defaults.

    code usable in standard and temp table revisited moving at the end check for identity, nullable, collation and default constraint; collation are explicited only if different from default.

    --Get the columns, their definitions and defaults.

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

    SELECT

    @FINALSQL = @FINALSQL

    + CASE

    WHEN COLS.[is_computed] = 1

    THEN @vbCrLf

    + QUOTENAME(COLS.[name])

    + ' '

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

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

    + CASE

    WHEN CALC.is_persisted = 1

    THEN ' PERSISTED'

    ELSE ''

    END

    ELSE @vbCrLf

    + QUOTENAME(COLS.[name])

    + ' '

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

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

    + CASE

    -- data types using precision and scaleIE NUMERIC(10,2)

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

    THEN '('

    + CONVERT(VARCHAR,COLS.[precision])

    + ','

    + CONVERT(VARCHAR,COLS.[scale])

    + ') '

    + SPACE( 9 - 3 + @TYPELEN-- 3 FOR '(,)' + 3 BYTE FOR PRECISION + 3 BYTE FOR SCALE

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

    - LEN(CONVERT(VARCHAR,COLS.[scale]))

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    -- data types using scale onlyIE DATETIME2(7)

    WHEN TYPE_NAME(COLS.[user_type_id]) IN ('datetime2','datetimeoffset','time')

    THEN '('

    + CONVERT(VARCHAR,COLS.[scale])

    + ') '

    + SPACE( 9 - 2 + @TYPELEN-- 2 FOR '()'

    - LEN(CONVERT(VARCHAR,COLS.[scale]))

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    --data type using max_lengthie VARCHAR(40)

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

    THEN CASE

    WHEN COLS.[max_length] = -1

    THEN '(max)'

    + SPACE( 10 + @TYPELEN

    - LEN('(MAX)')

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    ELSE '('

    + CONVERT(VARCHAR,COLS.[max_length])

    + ') '

    + SPACE(9-2 + @TYPELEN-- 2 FOR '()'

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

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    END

    --data type using max_length (BUT DOUBLED) ie NVARCHAR(40)

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

    THEN CASE

    WHEN COLS.[max_length] = -1

    THEN '(max)'

    + SPACE( 10 + @TYPELEN

    - LEN('(MAX)')

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    ELSE '('

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

    + ') '

    + SPACE( 9 - 2 + @TYPELEN

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

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    END

    -- all other data type defined without using parameterIE int, bigint, float, smalldatetime, bit, datetime, money, CUSTOM DATA TYPE, .....

    ELSE SPACE(10 + @TYPELEN - LEN(TYPE_NAME(COLS.[user_type_id])))

    END

    + 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

    --collate to comment out when not desired

    + CASE

    WHEN COLS.collation_name IS NULL

    or COLS.collation_name = @DbCollation -- only if column collation <> default collation

    THEN ''

    ELSE ' COLLATE ' + COLS.collation_name

    END

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    + CASE

    WHEN COLS.[default_object_id] = 0

    THEN ''

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

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

    ELSE ' CONSTRAINT ' + quotename(DEF.name) + ' DEFAULT ' + ISNULL(DEF.[definition] ,'')

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

    END --CASE cdefault

    END --iscomputed

    + ','

    -- FROM sys.columns COLS .........

    the float(53) data type never exist if i try to create a table declaring a field float ( 1 to 24) mssql will create a "real" field if i try to create a field as float or as float(25 to 53) mssql wil create a float field.

    ( tested on sql2008R2 and sql2014)

    Maurizio

  • I just ran across this Lowell, it was exactly what I was looking for and has saved me a lot of time (it's 8:50PM in Chicago and I'm still working)...

    This is an amazing proc - very brilliant work sir!

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

    -- Itzik Ben-Gan 2001

  • Alan.B (7/28/2016)


    I just ran across this Lowell, it was exactly what I was looking for and has saved me a lot of time (it's 8:50PM in Chicago and I'm still working)...

    This is an amazing proc - very brilliant work sir!

    Thank you very much Alan! I hope it's helping you as much as it helps me; I call it hundred of times a week, I think; keyboard shortcut for development makes it so handy.

    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, than for tis very usefull script.

    i modified some part of code in order to:

    Fix Problem when custom data type are defined using name greather than 16 char.

    Add handling for data type: binary, datetime2, datetimeoffset, time

    Fix FOREIGN KEYS handling when one foreign key is define on more then one field

    If you get the code and search for "mlm" you will see all modification done on v315 code.-- USAGE: exec sp_GetDDL GMACT

    -- or exec sp_GetDDL 'bob.example'

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

    -- or exec sp_GetDDL #temp

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

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

    -- http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt

    --Purpose: Script Any Table, Temp Table or Object

    --

    -- see the thread here for lots of details: http://www.sqlservercentral.com/Forums/Topic751783-566-7.aspx

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

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

    --

    -- 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 definition as a table...

    -- now it will return the view definition instead.

    -- V309a returns multi row recordset, one line per record

    -- V310a fixed the commented out code related to collation identified by moadh.bs @SSC

    -- changed the DEFAULT definitions to not include the default name.

    -- V310b Added PERSISTED to calculated columns where applicable

    -- V310b fixed COLLATE statement for temp tables

    -- V310c fixed NVARCHAR size misreported as doubled.

    -- V311 fixed issue where indexes did not identify if the column was ASC or DESC found by nikus @ SSC

    -- V311a fixed issue where indexes did not identify if the index was CLUSTERED or NONCLUSTERED found by nikus @ SSC 02/22/2013

    -- V312 got rid of all upper casing, and allowing all scripts to generate the exact object names in cases of case sensitive databases.

    -- now using the case sensitive name of the table passed: so of you did 'exec sp_GetDDL invoicedocs , it might return the script for InvoiceDocs, as that is how it is spelled in sys.objects.

    -- added if exists(drop table/procedure/function) statement to the scripting automatically.

    -- toggled the commented out code to list any default constraints by name, hopefully to be more accurate..

    -- formatting of index statements to be multi line for better readability

    --V314 03/30/2015

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

    -- scripts any object:table,#temptable procedure, function, view or trigger

    -- 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 account for spaces in names; previously an error for Applciation ID instead of [Application ID]

    --V315 Fixes Aliases and column names that prevented Case Sensitive collations from working.

    -- Adds code if the procedure scripted is a system object

    -- index scripts featuring filtered indexes is now included

    -- index scripts now include filegroup name and compression settings

    -- foreign key casecade delete/update settings now included as identified by Alberto aserio@SSC)

    -- Fixes related to scripting extended events as identified by Alberto aserio@SSC)

    --V316 07/27/2016 by mlm

    -- Fixed Problem when custom data type are defined using name greather than 16 char.

    -- Added handling for data tye: binary, datetime2, datetimeoffset, time

    -- Fixed FOREIGN KEYS handling when one foreign key is define on more then one field

    -- DROP PROCEDURE [dbo].[sp_GetDDL]

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

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

    @MARKSYSTEMOBJECT VARCHAR(MAX),

    @vbCrLf CHAR(2),

    @ISSYSTEMOBJECT INT,

    @PROCNAME VARCHAR(256),

    @input VARCHAR(MAX),

    @ObjectTypeFound VARCHAR(255)

    -- start added by mlm

    DECLARE@DbCollationVARCHAR(255) = convert(varchar(255) ,databasepropertyex(db_name(),'collation')),

    @TYPELENint,

    @FkNameVARCHAR(255) ,

    @FK_IDINT,

    @Ref_idINT,

    @FkOnDelINT ,

    @FkOnUpdINT ,

    @FkReplINT

    -- stop added by mlm

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

    -- 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,'sp_GetDDL') FROM sys.objects WHERE OBJECT_ID = @@PROCID

    IF @ISSYSTEMOBJECT IS NULL

    SELECT @ISSYSTEMOBJECT = ISNULL(is_ms_shipped,0),@PROCNAME = ISNULL(name,'sp_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

    @TBLNAME = [name],

    @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 ' + quotename(@TBLNAME) + ' FOUND'

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

    BEGIN

    PRINT '--GOIN TO TEMP PROCESSING'

    GOTO TEMPPROCESS

    END

    END

    ELSE

    BEGIN

    PRINT '--Non-Temp Table, ' + quotename(@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

    @TBLNAME = [name],

    @TABLE_ID = [OBJECT_ID],

    @ObjectTypeFound = type_desc

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

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

    AND [name] <> 'dtproperties'

    AND [name] = @TBLNAME

    AND [SCHEMA_ID] = SCHEMA_ID(@SCHEMANAME) ;

    IF ISNULL(@TABLE_ID,0) <> 0

    BEGIN

    --adding a drop statement.

    --adding a sp_ms_marksystemobject if needed

    SELECT @MARKSYSTEMOBJECT = CASE

    WHEN is_ms_shipped = 1

    THEN '

    GO

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

    --Mark as a system object

    EXECUTE sp_ms_marksystemobject ''' + quotename(@SCHEMANAME) +'.' + quotename(@TBLNAME) + '''

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

    '

    ELSE '

    GO

    '

    END

    FROM sys.objects OBJS

    WHERE object_id = @TABLE_ID

    --adding a drop statement.

    --adding a drop statement.

    IF @ObjectTypeFound = 'SYNONYM'

    BEGIN

    SELECT @FINALSQL =

    'IF EXISTS(SELECT * FROM sys.synonyms WHERE name = '''

    + name

    + ''''

    + ' AND base_object_name <> ''' + base_object_name + ''')'

    + @vbCrLf

    + ' DROP SYNONYM ' + quotename(name) + ''

    + @vbCrLf

    +'GO'

    + @vbCrLf

    +'IF NOT EXISTS(SELECT * FROM sys.synonyms WHERE name = '''

    + name

    + ''')'

    + @vbCrLf

    + 'CREATE SYNONYM ' + quotename(name) + ' FOR ' + base_object_name +';'

    from sys.synonyms

    WHERE [name] = @TBLNAME

    AND [SCHEMA_ID] = SCHEMA_ID(@SCHEMANAME);

    END

    ELSE

    BEGIN

    SELECT @FINALSQL =

    'IF OBJECT_ID(''' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TBLNAME) + ''') IS NOT NULL ' + @vbcrlf

    + 'DROP ' + CASE

    WHEN OBJS.[type] IN ('P')

    THEN ' PROCEDURE '

    WHEN OBJS.[type] IN ('V')

    THEN ' VIEW '

    WHEN OBJS.[type] IN ('TR')

    THEN ' TRIGGER '

    ELSE ' FUNCTION '

    END

    + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TBLNAME) + ' ' + @vbcrlf + 'GO' + @vbcrlf

    + def.definition + @MARKSYSTEMOBJECT

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

    END

    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

    )

    SELECT

    --row_number() over (order by ItemOrder) as ItemID,

    Item

    FROM ItemSplit;

    RETURN 0

    END

    ELSE

    BEGIN

    SET @FINALSQL = 'Object ' + quotename(@SCHEMANAME) + '.' + quotename(@TBLNAME) + ' does not exist in Database ' + quotename(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 = 'IF OBJECT_ID(''' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TBLNAME) + ''') IS NOT NULL ' + @vbcrlf

    + 'DROP TABLE ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TBLNAME) + ' ' + @vbcrlf + 'GO' + @vbcrlf

    + 'CREATE TABLE ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TBLNAME) + ' ( '

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

    SELECT

    @STRINGLEN = MAX(LEN(QUOTENAME(COLS.[name]))) + 1,-- modified by mlm

    @TYPELEN = MAX(LEN(typ.[name])) + 1-- added by mlm

    FROM sys.objects OBJS

    INNER JOIN sys.columns COLS

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

    AND OBJS.[object_id] = @TABLE_ID

    INNER JOIN sys.types typ-- addedd by mlm

    ON COLS.user_type_id=typ.user_type_id-- addedd by mlm

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

    --Get the columns, their definitions and defaults.

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

    -- start modified by mlm

    SELECT

    @FINALSQL = @FINALSQL

    + CASE

    WHEN COLS.[is_computed] = 1

    THEN @vbCrLf

    + QUOTENAME(COLS.[name])

    + ' '

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

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

    + CASE

    WHEN CALC.is_persisted = 1

    THEN ' PERSISTED'

    ELSE ''

    END

    ELSE @vbCrLf

    + QUOTENAME(COLS.[name])

    + ' '

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

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

    + CASE

    -- data types with precision and scaleIE DECIMAL(18,3), NUMERIC(10,2)

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

    THEN '('

    + CONVERT(VARCHAR,COLS.[precision])

    + ','

    + CONVERT(VARCHAR,COLS.[scale])

    + ') '

    + SPACE( 7 - 3 + @TYPELEN-- 3 FOR '(,)' + 2 BYTE FOR PRECISION + 2 BYTE FOR SCALE

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

    - LEN(CONVERT(VARCHAR,COLS.[scale]))

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    -- data types with scaleIE TIME(7)

    WHEN TYPE_NAME(COLS.[user_type_id]) IN ('datetime2','datetimeoffset','time')

    THEN '('

    + CONVERT(VARCHAR,COLS.[scale])

    + ') '

    + SPACE( 7 - 2 + @TYPELEN-- 2 FOR '()' + 3 BYTE FOR SCALE

    - LEN(CONVERT(VARCHAR,COLS.[scale]))

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    --data type with max_lengthie CHAR (44), VARCHAR(40), BINARY(5000),

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

    THEN CASE

    WHEN COLS.[max_length] = -1

    THEN '(max)'

    + SPACE( 8 + @TYPELEN

    - LEN('(MAX)')

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    ELSE '('

    + CONVERT(VARCHAR,COLS.[max_length])

    + ') '

    + SPACE(7 - 2 + @TYPELEN-- 2 FOR '()'

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

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    END

    --data type with max_length ( BUT DOUBLED) ie NCHAR(33), NVARCHAR(40)

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

    THEN CASE

    WHEN COLS.[max_length] = -1

    THEN '(max)'

    + SPACE( 8 + @TYPELEN

    - LEN('(MAX)')

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    ELSE '('

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

    + ') '

    + SPACE( 7 - 2 + @TYPELEN

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

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    END

    -- All other data type IE INT, DATETIME, MONEY, CUSTOM DATA TYPE,...

    ELSE SPACE(8 + @TYPELEN - LEN(TYPE_NAME(COLS.[user_type_id])))

    END

    + 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

    --collate to comment out when not desired

    + CASE

    WHEN COLS.collation_name IS NULL

    or COLS.collation_name = @DbCollation -- only if column collation <> default collation

    THEN ''

    ELSE ' COLLATE ' + COLS.collation_name

    END

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    -- stop modified by mlm

    + CASE

    WHEN COLS.[default_object_id] = 0

    THEN ''

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

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

    ELSE ' CONSTRAINT ' + quotename(DEF.name) + ' DEFAULT ' + ISNULL(DEF.[definition] ,'')

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

    END --CASE cdefault

    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] BIGINT,

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

    [has_filter] bit ,

    [filter_definition] VARCHAR(MAX),

    [currentFilegroupName] varchar(128),

    [CurrentCompression] varchar(128))

    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,

    IDX.[has_filter],

    IDX.[filter_definition],

    filz.name,

    ISNULL(p.data_compression_desc,'')

    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.filegroups filz ON IDX.data_space_id = filz.data_space_id

    INNER JOIN sys.partitions p ON IDX.object_id = p.object_id AND IDX.index_id = p.index_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 QUOTENAME(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 QUOTENAME(COLS.[name]) + ',' + ' '

    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

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

    SELECT @CONSTRAINTSQLS = @CONSTRAINTSQLS

    + CASE

    WHEN is_primary_key = 1 OR is_unique = 1

    THEN @vbCrLf

    + 'CONSTRAINT ' + quotename(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 [has_filter] = 1

    THEN ' ' + [filter_definition]

    ELSE ' '

    END

    + CASE WHEN fill_factor <> 0 OR [CurrentCompression] <> 'NONE'

    THEN ' WITH (' + CASE

    WHEN fill_factor <> 0

    THEN 'FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor)

    ELSE ''

    END

    + CASE

    WHEN fill_factor <> 0 AND [CurrentCompression] <> 'NONE' THEN ',DATA_COMPRESSION = ' + [CurrentCompression] + ' '

    WHEN fill_factor <> 0 AND [CurrentCompression] = 'NONE' THEN ''

    WHEN fill_factor = 0 AND [CurrentCompression] <> 'NONE' THEN 'DATA_COMPRESSION = ' + [CurrentCompression] + ' '

    ELSE ''

    END

    + ')'

    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 ' + type_desc + ' INDEX ' + quotename(index_name) + ' '

    + @vbCrLf

    + ' ON ' + quotename([schema_name]) + '.' + quotename([OBJECT_NAME])

    + ' (' + index_columns_key + ')'

    + CASE

    WHEN index_columns_include <> '---'

    THEN @vbCrLf + ' INCLUDE (' + index_columns_include + ')'

    ELSE ''

    END

    --2008 filtered indexes syntax

    + CASE

    WHEN has_filter = 1

    THEN @vbCrLf + ' WHERE ' + filter_definition

    ELSE ''

    END

    + CASE WHEN fill_factor <> 0 OR [CurrentCompression] <> 'NONE'

    THEN ' WITH (' + CASE

    WHEN fill_factor <> 0

    THEN 'FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor)

    ELSE ''

    END

    + CASE

    WHEN fill_factor <> 0 AND [CurrentCompression] <> 'NONE' THEN ',DATA_COMPRESSION = ' + [CurrentCompression]+' '

    WHEN fill_factor <> 0 AND [CurrentCompression] = 'NONE' THEN ''

    WHEN fill_factor = 0 AND [CurrentCompression] <> 'NONE' THEN 'DATA_COMPRESSION = ' + [CurrentCompression]+' '

    ELSE ''

    END

    + ')'

    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 ' + quotename(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

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

    --FOREIGN KEYS

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

    -- start modified by mlm

    SET @FKSQLS = ''

    DECLARE FkCur CURSOR FOR (

    SELECTfk.name, FK.OBJECT_ID, fk.referenced_object_id, fk.delete_referential_action,fk.update_referential_action,fk.is_not_for_replication

    FROMsys.foreign_keys fk

    WHEREfk.parent_object_id = @TABLE_ID

    )

    OPEN FkCur;

    FETCH NEXT FROM FkCur INTO @FkName, @FK_ID, @Ref_id, @FkOnDel, @FkOnUpd ,@FkRepl

    WHILE @@FETCH_STATUS = 0 and @FkName is not null

    BEGIN

    SELECT@FKSQLS= @FKSQLS + CHAR(13)+CHAR(10)

    + 'CONSTRAINT '

    + quotename(@FkName)

    + isnull(space(30- len(@FkName) ),'')

    + ' FOREIGN KEY ( '

    SELECT @FKSQLS=@FKSQLS+quotename(pcol.name) + ', '

    FROM sys.foreign_key_columns fkc

    inner join sys.columns pcol on pcol.object_id=fkc.parent_object_idand pcol.column_id=parent_column_id

    WHERE fkc.constraint_object_id = @FK_ID

    SET @FKSQLS=LEFT( @FKSQLS, LEN(@FKSQLS)-1) -- tolgo ultima virola+spazio

    +') REFERENCES '+ quotename(OBJECT_NAME(@Ref_id)) + ' ('

    SELECT @FKSQLS=@FKSQLS+quotename(rcol.name) + ', '

    FROM sys.foreign_key_columns fkc

    inner join sys.columns rcol on rcol.object_id=fkc.referenced_object_id and rcol.column_id=referenced_column_id

    WHERE fkc.constraint_object_id = @FK_ID

    SET @FKSQLS=LEFT( @FKSQLS, LEN(@FKSQLS)-1) +')' -- tolgo ultima virola+spazio e chiudo parentesi

    + CASEWHEN @FkOnDel = 1 THEN N'ON DELETE CASCADE '

    WHEN @FkOnDel = 2 THEN N'ON DELETE SET NULL '

    WHEN @FkOnDel = 3 THEN N'ON DELETE SET DEFAULT '

    ELSE ''

    END

    + CASEWHEN @FkOnUpd = 1 THEN N'ON UPDATE CASCADE '

    WHEN @FkOnUpd = 2 THEN N'ON UPDATE SET NULL '

    WHEN @FkOnUpd = 3 THEN N'ON UPDATE SET DEFAULT '

    ELSE ''

    END

    + CASEWHEN @FkRepl = 1 THEN N'NOT FOR REPLICATION '

    ELSE ''

    END

    FETCH NEXT FROM FkCur INTO @FkName, @FK_ID, @Ref_id, @FkOnDel, @FkOnUpd ,@FkRepl

    END

    CLOSE FkCur

    DEALLOCATE FkCur

    -- stop modified by mlm

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

    --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),OBJS.schema_id) + ' AND [name] = ''' + quotename(OBJECT_NAME(COLS.[rule_object_id])) + ''')' + @vbCrLf

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

    + 'EXEC sp_binderule ' + quotename(OBJS.[name]) + ', ''' + quotename(OBJECT_NAME(COLS.[object_id])) + '.' + quotename(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

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

    --TRIGGERS

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

    SET @TRIGGERSTATEMENT = ''

    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

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

    --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 = ' + quotename(@SCHEMANAME) + ',

    @level1type = N''TABLE'', @level1name = ' + quotename(@TBLNAME) + ';'

    --SELECT objtype, objname, name, value

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

    ;WITH obj AS (

    SELECT split.a.value('.', 'VARCHAR(20)') AS name

    FROM (

    SELECT CAST ('<M>' + REPLACE('column,constraint,index,trigger,parameter', ',', '</M><M>') + '</M>' AS XML) AS data

    ) AS A

    CROSS APPLY data.nodes ('/M') AS split(a)

    )

    SELECT

    @EXTENDEDPROPERTIES =

    @EXTENDEDPROPERTIES + @vbCrLf + @vbCrLf +

    'EXEC sys.sp_addextendedproperty

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

    @level0type = N''SCHEMA'', @level0name = ' + quotename(@SCHEMANAME) + ',

    @level1type = N''TABLE'', @level1name = ' + quotename(@TBLNAME) + ',

    @level2type = N''' + UPPER(obj.name) + ''', @level2name = ' + quotename(lep.[objname]) + ';'

    --SELECT objtype, objname, name, value

    FROM obj

    CROSS APPLY fn_listextendedproperty (NULL, 'schema', @SCHEMANAME, 'table', @TBLNAME, obj.name, NULL) AS lep; 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 @input = @vbCrLf

    + @FINALSQL

    + @INDEXSQLS

    + @RULESCONSTSQLS

    + @TRIGGERSTATEMENT

    + @EXTENDEDPROPERTIES

    SELECT @input AS Item;

    RETURN 0;

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

    -- 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 temp Table, Continue Processing

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

    SELECT

    @FINALSQL = 'IF OBJECT_ID(''tempdb.' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TBLNAME) + ''') IS NOT NULL ' + @vbcrlf

    + 'DROP TABLE ' + QUOTENAME(@SCHEMANAME) + '.' + QUOTENAME(@TBLNAME) + ' ' + @vbcrlf + 'GO' + @vbcrlf

    + 'CREATE TABLE ' + quotename(@SCHEMANAME) + '.' + quotename(@TBLNAME) + ' ( '

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

    SELECT

    @STRINGLEN = MAX(LEN(QUOTENAME(COLS.[name]))) + 1,-- modified by mlm

    @TYPELEN = MAX(LEN(typ.[name])) + 1-- addedd by mlm

    FROM tempdb.sys.objects OBJS

    INNER JOIN tempdb.sys.columns COLS

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

    AND OBJS.[object_id] = @TABLE_ID

    INNER JOIN tempdb.sys.types typ-- addedd by mlm

    ON COLS.user_type_id=typ.user_type_id-- addedd by mlm

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

    --Get the columns, their definitions and defaults.

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

    -- start modified by mlm

    SELECT

    @FINALSQL = @FINALSQL

    + CASE

    WHEN COLS.[is_computed] = 1

    THEN @vbCrLf

    + QUOTENAME(COLS.[name])

    + ' '

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

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

    + CASE

    WHEN CALC.is_persisted = 1

    THEN ' PERSISTED'

    ELSE ''

    END

    ELSE @vbCrLf

    + QUOTENAME(COLS.[name])

    + ' '

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

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

    + CASE

    -- data types with precision and scaleIE NUMERIC(10,2)

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

    THEN '('

    + CONVERT(VARCHAR,COLS.[precision])

    + ','

    + CONVERT(VARCHAR,COLS.[scale])

    + ') '

    + SPACE( 9 - 3 + @TYPELEN-- 3 FOR '(,)' + 3 BYTE FOR PRECISION + 3 BYTE FOR SCALE

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

    - LEN(CONVERT(VARCHAR,COLS.[scale]))

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    -- data types with scaleIE DATETIME2(10,2)

    WHEN TYPE_NAME(COLS.[user_type_id]) IN ('datetime2','datetimeoffset','time')

    THEN '('

    + CONVERT(VARCHAR,COLS.[scale])

    + ') '

    + SPACE( 9 - 2 + @TYPELEN-- 3 FOR '(,)' + 3 BYTE FOR PRECISION + 3 BYTE FOR SCALE

    - LEN(CONVERT(VARCHAR,COLS.[scale]))

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    --data type with max_lengthie VARCHAR(40)

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

    THEN CASE

    WHEN COLS.[max_length] = -1

    THEN '(max)'

    + SPACE( 10 + @TYPELEN

    - LEN('(MAX)')

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    ELSE '('

    + CONVERT(VARCHAR,COLS.[max_length])

    + ') '

    + SPACE(9-2 + @TYPELEN-- 2 FOR '()'

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

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    END

    --DATA TYPE WITH MAX_LENGTH ( BUT DOUBLED) ie NVARCHAR(40)

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

    THEN CASE

    WHEN COLS.[max_length] = -1

    THEN '(max)'

    + SPACE( 10 + @TYPELEN

    - LEN('(MAX)')

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    ELSE '('

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

    + ') '

    + SPACE( 9 - 2 + @TYPELEN

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

    - LEN(TYPE_NAME(COLS.[user_type_id]))

    )

    END

    -- ALL OTHER DATA TYPEIE INT, DATETIME, MONEY, CUSTOM DATA TYPE,...

    ELSE SPACE(10 + @TYPELEN - LEN(TYPE_NAME(COLS.[user_type_id])))

    END

    + 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

    --collate to comment out when not desired

    + CASE

    WHEN COLS.collation_name IS NULL

    or COLS.collation_name = @DbCollation -- only if column collation <> default collation

    THEN ''

    ELSE ' COLLATE ' + COLS.collation_name

    END

    + CASE

    WHEN COLS.[is_nullable] = 0

    THEN ' NOT NULL'

    ELSE ' NULL'

    END

    + CASE

    WHEN COLS.[default_object_id] = 0

    THEN ''

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

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

    ELSE ' CONSTRAINT ' + quotename(DEF.name) + ' DEFAULT ' + ISNULL(DEF.[definition] ,'')

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

    END --CASE cdefault

    -- stop modified by mlm

    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] BIGINT,

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

    [has_filter] bit ,

    [filter_definition] VARCHAR(MAX),

    [currentFilegroupName] varchar(128),

    [CurrentCompression] varchar(128))

    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,

    IDX.has_filter,

    IDX.filter_definition,

    filz.name,

    ISNULL(p.data_compression_desc,'')

    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 sys.filegroups filz ON IDX.data_space_id = filz.data_space_id

    INNER JOIN sys.partitions p ON IDX.object_id = p.object_id AND IDX.index_id = p.index_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 QUOTENAME(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 QUOTENAME(COLS.[name]) + ',' + ' '

    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

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

    SELECT @CONSTRAINTSQLS = @CONSTRAINTSQLS

    + CASE

    WHEN is_primary_key = 1 OR is_unique = 1

    THEN @vbCrLf

    + 'CONSTRAINT ' + quotename(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 [has_filter] = 1

    THEN ' ' + [filter_definition]

    ELSE ' '

    END

    + CASE WHEN fill_factor <> 0 OR [CurrentCompression] <> 'NONE'

    THEN ' WITH (' + CASE

    WHEN fill_factor <> 0

    THEN 'FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor)

    ELSE ''

    END

    + CASE

    WHEN fill_factor <> 0 AND [CurrentCompression] <> 'NONE' THEN ',DATA_COMPRESSION = ' + [CurrentCompression] + ' '

    WHEN fill_factor <> 0 AND [CurrentCompression] = 'NONE' THEN ''

    WHEN fill_factor = 0 AND [CurrentCompression] <> 'NONE' THEN 'DATA_COMPRESSION = ' + [CurrentCompression] + ' '

    ELSE ''

    END

    + ')'

    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

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

    --indexes

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

    SELECT @INDEXSQLS = @INDEXSQLS

    + CASE

    WHEN is_primary_key = 0 OR is_unique = 0

    THEN @vbCrLf

    + 'CREATE ' + type_desc + ' INDEX ' + quotename(index_name) + ' '

    + @vbCrLf

    + ' ON ' + quotename([schema_name]) + '.' + quotename([OBJECT_NAME])

    + ' (' + index_columns_key + ')'

    + CASE

    WHEN index_columns_include <> '---'

    THEN @vbCrLf + ' INCLUDE (' + index_columns_include + ')'

    ELSE ''

    END

    --2008 filtered indexes syntax

    + CASE

    WHEN has_filter = 1

    THEN @vbCrLf + ' WHERE ' + filter_definition

    ELSE ''

    END

    + CASE WHEN fill_factor <> 0 OR [CurrentCompression] <> 'NONE'

    THEN ' WITH (' + CASE

    WHEN fill_factor <> 0

    THEN 'FILLFACTOR = ' + CONVERT(VARCHAR(30),fill_factor)

    ELSE ''

    END

    + CASE

    WHEN fill_factor <> 0 AND [CurrentCompression] <> 'NONE' THEN ',DATA_COMPRESSION = ' + [CurrentCompression] + ' '

    WHEN fill_factor <> 0 AND [CurrentCompression] = 'NONE' THEN ''

    WHEN fill_factor = 0 AND [CurrentCompression] <> 'NONE' THEN 'DATA_COMPRESSION = ' + [CurrentCompression] + ' '

    ELSE ''

    END

    + ')'

    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

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

    --CHECK Constraints

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

    SET @CHECKCONSTSQLS = ''

    SELECT

    @CHECKCONSTSQLS = @CHECKCONSTSQLS

    + @vbCrLf

    + ISNULL('CONSTRAINT ' + quotename(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

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

    --FOREIGN KEYS

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

    -- start modified by mlm

    SET @FKSQLS = ''

    DECLARE FkCur CURSOR FOR (

    SELECTfk.name, FK.OBJECT_ID, fk.referenced_object_id, fk.delete_referential_action,fk.update_referential_action,fk.is_not_for_replication

    FROMsys.foreign_keys fk

    WHEREfk.parent_object_id = @TABLE_ID

    )

    OPEN FkCur;

    FETCH NEXT FROM FkCur INTO @FkName, @FK_ID, @Ref_id, @FkOnDel, @FkOnUpd ,@FkRepl

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SELECT@FKSQLS= @FKSQLS + CHAR(13)+CHAR(10)+'CONSTRAINT ' + quotename(@FkName) + ' FOREIGN KEY ( '

    SELECT @FKSQLS=@FKSQLS+quotename(pcol.name) + ', '

    FROM sys.foreign_key_columns fkc

    inner join sys.columns pcol on pcol.object_id=fkc.parent_object_idand pcol.column_id=parent_column_id

    WHERE fkc.constraint_object_id = @FK_ID

    SET @FKSQLS=LEFT( @FKSQLS, LEN(@FKSQLS)-2) -- tolgo ultima virola+spazio

    +') REFERENCES '+ quotename(OBJECT_NAME(@Ref_id)) + ' ('

    SELECT @FKSQLS=@FKSQLS+quotename(rcol.name) + ', '

    FROM sys.foreign_key_columns fkc

    inner join sys.columns rcol on rcol.object_id=fkc.referenced_object_id and rcol.column_id=referenced_column_id

    WHERE fkc.constraint_object_id = @FK_ID

    SET @FKSQLS=LEFT( @FKSQLS, LEN(@FKSQLS)-2) +')' -- tolgo ultima virola+spazio e chiudo parentesi

    + CASEWHEN @FkOnDel = 1 THEN N'ON DELETE CASCADE '

    WHEN @FkOnDel = 2 THEN N'ON DELETE SET NULL '

    WHEN @FkOnDel = 3 THEN N'ON DELETE SET DEFAULT '

    ELSE ''

    END

    + CASEWHEN @FkOnUpd = 1 THEN N'ON UPDATE CASCADE '

    WHEN @FkOnUpd = 2 THEN N'ON UPDATE SET NULL '

    WHEN @FkOnUpd = 3 THEN N'ON UPDATE SET DEFAULT '

    ELSE ''

    END

    + CASEWHEN @FkRepl = 1 THEN N'NOT FOR REPLICATION '

    ELSE ''

    END

    FETCH NEXT FROM FkCur INTO @FkName, @FK_ID, @Ref_id, @FkOnDel, @FkOnUpd ,@FkRepl

    END

    CLOSE FkCur

    DEALLOCATE FkCur

    -- stop modified by mlm

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

    --RULES

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

    SET @RULESCONSTSQLS = ''

    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] = ''' + quotename(OBJECT_NAME(COLS.[rule_object_id])) + ''')' + @vbCrLf

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

    + 'EXEC sp_binderule ' + quotename(OBJS.[name]) + ', ''' + quotename(OBJECT_NAME(COLS.[object_id])) + '.' + quotename(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

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

    --TRIGGERS

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

    SET @TRIGGERSTATEMENT = ''

    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

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

    --NEW SECTION QUERY ALL EXTENDED PROPERTIES

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

    SET @EXTENDEDPROPERTIES = ''

    SELECT @EXTENDEDPROPERTIES =

    @EXTENDEDPROPERTIES + @vbCrLf +

    'EXEC tempdb.sys.sp_addextendedproperty

    @name = N''' + [name] + ''', @value = N''' + REPLACE(CONVERT(VARCHAR(MAX),[VALUE]),'''','''''') + ''',

    @level0type = N''SCHEMA'', @level0name = ' + quotename(@SCHEMANAME + ',

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

    --SELECT objtype, objname, name, value

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

    --OMacoder suggestion for column extended properties http://www.sqlservercentral.com/Forums/FindPost1651606.aspx

    SELECT @EXTENDEDPROPERTIES =

    @EXTENDEDPROPERTIES + @vbCrLf +

    'EXEC sys.sp_addextendedproperty

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

    @level0type = N''SCHEMA'', @level0name = ' + quotename(@SCHEMANAME) + ',

    @level1type = N''TABLE'', @level1name = ' + quotename(@TBLNAME) + ',

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

    --SELECT objtype, objname, name, value

    FROM fn_listextendedproperty (NULL, 'schema', @SCHEMANAME, 'table', @TBLNAME, 'column', 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 @input = @vbCrLf

    + @FINALSQL

    + @INDEXSQLS

    + @RULESCONSTSQLS

    + @TRIGGERSTATEMENT

    + @EXTENDEDPROPERTIES

    SELECT @input AS Item;

    RETURN 0;

    END --PROC

  • thanks for the code review,m.martinelli! you've pointed out items taht made some vast improvements!

    i incorporated all your ideas, but in my own coding style;

    i had not tested against the newer datatypes with scale, that was a very welcome addition.

    excellent catch on the usertype size, i simple created a variable and replaced the hardcoded 16's with the variable so everything aligns nicely.

    create type dbo.AReallyLongObjectNameForTestingThisFunctionality FROM varchar(30) NULL

    SELECT @ObjectDataTypeLen = MAX(LEN(name)) from sys.types

    i had already scripted multi-column-foreign keys in one of my own scripts in a set based manner, so i grabbed that code instead of using the cursor.

    here's an exmaple of the scripting i alreayd used elsewhere, and migrated to sp_GetDDL/sp_GetDDLa:

    IF OBJECT_ID('[dbo].[Example]') IS NOT NULL

    DROP TABLE [dbo].[Example]

    GO

    CREATE TABLE [dbo].[Example] (

    [City] VARCHAR(30) NOT NULL,

    [StateCode] VARCHAR(2) NOT NULL,

    [ZipCode] VARCHAR(9) NOT NULL,

    [OtherColumns] VARCHAR(30) NULL,

    CONSTRAINT [PK__City_StateCode_ZipCode] PRIMARY KEY CLUSTERED ([City] asc, [StateCode] asc, [ZipCode] asc) )

    IF OBJECT_ID('[dbo].[Address]') IS NOT NULL

    DROP TABLE [dbo].[Address]

    GO

    CREATE TABLE [dbo].[Address] (

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

    [Address1] VARCHAR(50) NULL,

    [Address2] VARCHAR(50) NULL,

    [City] VARCHAR(30) NOT NULL,

    [StateCode] VARCHAR(2) NOT NULL,

    [ZipCode] VARCHAR(9) NOT NULL,

    CONSTRAINT [PK__Address__091C2A1B0BC6C43E] PRIMARY KEY CLUSTERED ([AddressID] asc) ,

    CONSTRAINT [FK__City_StateCode_ZipCode] FOREIGN KEY (City,StateCode,ZipCode) REFERENCES [dbo].[Example] (City,StateCode,ZipCode) ON UPDATE NO ACTION ON DELETE NO ACTION )

    GO

    SELECT

    DISTINCT

    --FK must be added AFTER the PK/unique constraints are added back.

    850 AS ExecutionOrder,

    'ALTER TABLE '

    + QUOTENAME(schema_name(conz.schema_id) )

    + '.'

    + QUOTENAME(OBJECT_NAME(conz.parent_object_id))

    + ' ADD CONSTRAINT '

    + QUOTENAME(conz.name)

    + ' FOREIGN KEY ('

    + ChildCollection.ChildColumns

    + ') REFERENCES '

    + QUOTENAME(SCHEMA_NAME(conz.schema_id))

    + '.'

    + QUOTENAME(OBJECT_NAME(conz.referenced_object_id))

    + ' (' + ParentCollection.ParentColumns

    + ') '

    + ' ON UPDATE ' + CASE conz.update_referential_action

    WHEN 0 THEN 'NO ACTION '

    WHEN 1 THEN 'CASCADE '

    WHEN 2 THEN 'SET NULL '

    ELSE 'SET DEFAULT '

    END

    + ' ON DELETE ' + CASE conz.delete_referential_action

    WHEN 0 THEN 'NO ACTION '

    WHEN 1 THEN 'CASCADE '

    WHEN 2 THEN 'SET NULL '

    ELSE 'SET DEFAULT '

    END

    + CASE conz.is_not_for_replication

    WHEN 1 THEN ' NOT FOR REPLICATION '

    ELSE ''

    END

    + ';' AS Command

    FROM sys.foreign_keys conz

    INNER JOIN sys.foreign_key_columns colz

    ON conz.object_id = colz.constraint_object_id

    INNER JOIN (--gets my child tables column names

    SELECT

    conz.name,

    ChildColumns = STUFF((SELECT

    ',' + REFZ.name

    FROM sys.foreign_key_columns fkcolz

    INNER JOIN sys.columns REFZ

    ON fkcolz.parent_object_id = REFZ.object_id

    AND fkcolz.parent_column_id = REFZ.column_id

    WHERE fkcolz.parent_object_id = conz.parent_object_id

    AND fkcolz.constraint_object_id = conz.object_id

    ORDER BY

    fkcolz.constraint_column_id

    FOR XML PATH('')), 1, 1, '')

    FROM sys.foreign_keys conz

    INNER JOIN sys.foreign_key_columns colz

    ON conz.object_id = colz.constraint_object_id

    GROUP BY

    conz.name,

    conz.parent_object_id,--- without GROUP BY multiple rows are returned

    conz.object_id

    ) ChildCollection

    ON conz.name = ChildCollection.name

    INNER JOIN (--gets the parent tables column names for the FK reference

    SELECT

    conz.name,

    ParentColumns = STUFF((SELECT

    ',' + REFZ.name

    FROM sys.foreign_key_columns fkcolz

    INNER JOIN sys.columns REFZ

    ON fkcolz.referenced_object_id = REFZ.object_id

    AND fkcolz.referenced_column_id = REFZ.column_id

    WHERE fkcolz.referenced_object_id = conz.referenced_object_id

    AND fkcolz.constraint_object_id = conz.object_id

    ORDER BY fkcolz.constraint_column_id

    FOR XML PATH('')), 1, 1, '')

    FROM sys.foreign_keys conz

    INNER JOIN sys.foreign_key_columns colz

    ON conz.object_id = colz.constraint_object_id

    -- AND colz.parent_column_id

    GROUP BY

    conz.name,

    conz.referenced_object_id,--- without GROUP BY multiple rows are returned

    conz.object_id

    ) ParentCollection

    ON conz.name = ParentCollection.name;

    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!

  • I continue to periodically get only the last column returned for a table definition. I thought that putting the last CASE ELSE in the "Get the columns, their definitions and defaults." section fixed it. But the issue still pops up - usually in databases that were created in SQL 2005. Asked the question in a forum with a mocked up example and someone pointed me to KB article 287515. I could not find the exact article but found the original issue logged in Microsoft Connect along with the response. It is exactly the issue that I am having. Basically it is saying not to use SELECT @Variable + @Variable + @something .... to concatenate row data. The Connect response gives several different workarounds. I added "FOR XML PATH (''),TYPE).value('.','varchar(max)')" and viola! it worked as expected - all columns were returned. And the best part was that the change took only a couple of minutes to make.

  • Hi, Lowell,

    great work, thank you! I added your procedure to my toolbox; just renamed it to GetTableDDL because I have a simple SP called GetObjectDDL for stored procs, functions etc. (which is of course embarassingly simple, especially compared to your proc).

    Best regards

    JP

    ________________________________________________________
    If you set out to do something, something else must be done first.

  • Hi JP,

    Can you also please post ur "GetObjectDDL" here so we all can benefit from it. 

    With Regards,
    B

  • Qur7, look 3 post ago, at the end of last reply by Lowell, there are two links.

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

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

    So.....

    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 team, new user here.  I've been experimenting with your script, and I have found a bug related to foreign key constraint declarations, in the case when they reference tables in a different schema outside the created table's own schema.

    Issue

    In my case, I have in my database a schema called Insurance with a table called Policy ([Insurance].[Policy]).  It references a foreign key in a schema called Common on a table called Account ([Common].[Account]).  The resulting CREATE TABLE code from your script created my foreign key relationship to  [Insurance].[Account] (which doesn't exist) instead of to [Common].[Account] as it should have.  

    Cause
    This seems to be occurring because there is an error in the query for foreign key constraints, around lines 737-775.  The current query assumes that the schema_id of the entry in the sys.foreign_keys table refers to the target schema of the foreign key relationship, when in fact it refers to the owner schema of the constraint.  In my case, this value refers to the Insurance schema because the FK constraint exists on the Policy table in that schema, but the target actually exists in the Common schema.  The solution is to add an INNER JOIN to the query to the sys.objects table based on the sys.foreign_keys.referenced_object_id field value, and use that schema_id value as the target schema for the foreign key relationship constraint.

    Fix Details
    The fix I implemented was to add a new inner join at line 772 as follows, to a new table I referenced as [schm]:

    INNER JOIN sys.objects schm
       ON conz.referenced_object_id = schm.object_id

    And then replace line 746 from this:
    + QUOTENAME(SCHEMA_NAME(conz.schema_id))
    to this:
    + QUOTENAME(SCHEMA_NAME(schm.schema_id))

    I have attached my corrected script to this comment, based on the what I think is latest version of this script found at http://www.stormrage.com/SQLStuff/sp_GetDDL_Latest.txt.

    Reproduction
    Below is the SQL in question for my original table, and the SQL created by your tool (with the related FK constraints underlined in each case).

    Here is my original table's create SQL, as exported by the Management Studio's Script Table As -> Create To option:
    CREATE TABLE [Insurance].[Policy](
        [Id] [uniqueidentifier] NOT NULL,
        -- ...
        [AccountId] [uniqueidentifier] NOT NULL,
        -- ...
        CONSTRAINT [PK_Policy] PRIMARY KEY CLUSTERED ([Id] ASC)
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    GO

    ALTER TABLE [Insurance].[Policy] WITH CHECK ADD CONSTRAINT [FK_Policy_Account] FOREIGN KEY([AccountId])
    REFERENCES [Common].[Account] ([Id])
    GO

    Here is the result of your script:
    CREATE TABLE [Insurance].[Policy] (
    [Id]       UNIQUEIDENTIFIER        NOT NULL,
    -- ...
    [AccountId]     UNIQUEIDENTIFIER        NOT NULL,
    -- ...
    CONSTRAINT [PK_Policy] PRIMARY KEY CLUSTERED  ([Id] asc) ,
    CONSTRAINT [FK_Policy_Account] FOREIGN KEY (AccountId) REFERENCES [Insurance].[Account] (Id)

Viewing 15 posts - 91 through 105 (of 117 total)

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