Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Generate DDL script to create table and all constraints Expand / Collapse
Author
Message
Posted Wednesday, March 21, 2012 10:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 30, 2014 6:59 AM
Points: 12, Visits: 136
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.
Post #1270315
Posted Wednesday, March 21, 2012 10:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 19, 2013 4:19 AM
Points: 264, Visits: 189
Here's my take on it the column type generation, which includes computed columns, UDTs and the numeric & decimal types:


SELECT QUOTENAME(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
FROM sys.columns c
INNER JOIN sys.objects o
ON c.[object_id] = o.[object_id]
INNER JOIN sys.types t
ON c.system_type_id = t.system_type_id
AND c.user_type_id = t.user_type_id
LEFT JOIN sys.schemas s
ON t.[schema_id] = s.[schema_id]
LEFT JOIN sys.computed_columns cc
ON c.[object_id] = cc.[object_id]
AND c.column_id = cc.column_id
WHERE o.name = 'Table_1'
ORDER BY c.column_id



Post #1270346
Posted Wednesday, March 21, 2012 12:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 30, 2014 6:59 AM
Points: 12, Visits: 136
Thanks, this helps. I will update the script accordingly.
Post #1270407
Posted Monday, October 1, 2012 11:48 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 6:20 AM
Points: 247, Visits: 800
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'.



Post #1366687
Posted Monday, October 1, 2012 12:25 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, January 30, 2014 6:59 AM
Points: 12, Visits: 136
I uploaded a new version of the script, should be there in a day.
Post #1366699
Posted Monday, October 8, 2012 8:06 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 6:20 AM
Points: 247, Visits: 800
Seems like it's working better as I'm getting output now.

But this error is coming up:

Msg 4145, Level 15, State 1, Line 35
An expression of non-boolean type specified in a context where a condition is expected, near 'name'.
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near 'o'.




Post #1369834
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse