Post reply

Generate DDL script to create table and all constraints

  • March 21, 2009 at 4:09 am

    #202549

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

  • February 26, 2010 at 3:00 am

    #1125452

    right? this scrips?

  • June 25, 2010 at 2:19 am

    #1184853

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

  • June 25, 2010 at 6:41 am

    #1184960

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

  • June 25, 2010 at 6:48 am

    #1184965

    Hi,

    thank's for response,

    does it work in SQL2005 please ?

  • June 25, 2010 at 6:51 am

    #1184966

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

  • November 3, 2011 at 4:17 am

    #1402558

    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 😀

  • March 21, 2012 at 4:49 am

    #1462077

    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?

  • March 21, 2012 at 6:33 am

    #1462097

    Can you send the structure of your table?

  • March 21, 2012 at 8:33 am

    #1462178

    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

  • March 21, 2012 at 10:14 am

    #1462269

    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.

  • March 21, 2012 at 10:52 am

    #1462295

    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]

  • March 21, 2012 at 12:03 pm

    #1462352

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

  • October 1, 2012 at 11:48 am

    #1544076

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

  • October 1, 2012 at 12:25 pm

    #1544086

    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