Generate DDL script to create table and all constraints

  • Comments posted to this topic are about the item Generate DDL script to create table and all constraints

  • right? this scrips?

  • Hello many pb when compiling SP

    Msg 102, Level 15, State 1, Line 2

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 13

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 21

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 22

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 22

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 23

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 23

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 24

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 24

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 26

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 28

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 32

    Must declare the scalar variable "@Create_Table_Ind".

    Msg 156, Level 15, State 1, Line 76

    Incorrect syntax near the keyword 'THEN'.

    Msg 137, Level 15, State 2, Line 88

    Must declare the scalar variable "@PK_Ind".

    Msg 137, Level 15, State 2, Line 150

    Must declare the scalar variable "@FK_Ind".

    Msg 137, Level 15, State 2, Line 194

    Must declare the scalar variable "@Check_Ind".

    Msg 137, Level 15, State 2, Line 213

    Must declare the scalar variable "@Default_Ind".

    Msg 102, Level 15, State 1, Line 252

    Incorrect syntax near 'END'.

  • This SP should be used in SQL Server 2008 version and use the signature as mentioned at the end of the SP.

  • Hi,

    thank's for response,

    does it work in SQL2005 please ?

  • It can but the SP will have to be tweaked to make it work in 2005 ...

  • This is a great script - really helpful - thank you!

    Can I suggest a tweak for computed columns too?

    Computed columns get created as a regular columns such as varchar(100) but if you left join to sys.computed_columns and use the [definition] column you end up with a column definition like this:

    [Fullname] AS (([Firstname]+' ')+[Lastname]),

    Hope this helps someone else out there 😀

  • Not sure if I have done something wrong here, but when generating the script for a table it's not generating the size for DECIMAL & NUMERIC columns?

  • Can you send the structure of your table?

  • This is just a table I created to test:

    CREATE TABLE [dbo].[Table_1](

    [col1] [nchar](10) NOT NULL,

    [col2] [numeric](25, 12) NULL,

    [col3] [xml] NULL,

    [col4] [decimal](18, 0) NULL,

    [col5] [varchar](max) NULL,

    [col6] [varbinary](50) NULL,

    [col7] [money] NULL,

    [col8] [ntext] NULL,

    [col9] [text] NULL,

    [col10] [float] NULL,

    [col11] AS ([col5]+'aaa'),

    [col12] [dbo].[ga_money] NOT NULL

    )

    Obviously ga_money is a user defined data type

  • Obviously the UDTs will not work with this script for now. I have tried to fix the issue that you highlihted and uploaded the script again. You may have to wait for a day before the script is published once again and visible to you.

  • Here's my take on it the column type generation, which includes computed columns, UDTs and the numeric & decimal types:

    [font="Courier New"]

    SELECTQUOTENAME(c.name) + ' '

    +CASE WHEN c.is_computed = 0 THEN

    CASE t.is_user_defined WHEN 1 THEN QUOTENAME(s.name) + '.' ELSE '' END +

    QUOTENAME(t.name) +

    CASE

    WHEN t.name IN ('binary','varbinary','char','varchar')

    THEN '(' + CASE c.max_length WHEN -1 THEN 'max' ELSE CAST(c.max_length AS VARCHAR(10)) END + ')'

    WHEN t.name IN ('nchar','nvarchar')

    THEN '(' + CASE c.max_length WHEN -1 THEN 'max' ELSE CAST(c.max_length/2 AS VARCHAR(10)) END + ')'

    WHEN t.name IN('numeric','decimal')

    THEN '(' + CAST(c.[precision] AS VARCHAR(10)) + ', ' + CAST(c.[scale] AS VARCHAR(10))+ ')'

    ELSE ''

    END + CASE c.is_nullable WHEN 0 THEN ' NOT NULL' ELSE ' NULL' END

    ELSE ' AS ' + cc.[definition]

    END AS dataType

    FROMsys.columns c

    INNERJOIN sys.objects o

    ONc.[object_id] = o.[object_id]

    INNERJOIN sys.types t

    ONc.system_type_id = t.system_type_id

    ANDc.user_type_id = t.user_type_id

    LEFTJOIN sys.schemas s

    ONt.[schema_id] = s.[schema_id]

    LEFTJOIN sys.computed_columns cc

    ONc.[object_id] = cc.[object_id]

    ANDc.column_id = cc.column_id

    WHEREo.name = 'Table_1'

    ORDERBY c.column_id

    [/font]

  • Thanks, this helps. I will update the script accordingly.

  • I'm on SQL 2008:

    When I exec I get:

    Msg 102, Level 15, State 1, Line 35

    Incorrect syntax near 'Schema_Name'.

    Msg 102, Level 15, State 1, Line 37

    Incorrect syntax near 'obje'.

  • I uploaded a new version of the script, should be there in a day.

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

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