Generate DDL script to create table and all constraints

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by VPSD Gupta » Generate DDL script to create table and all...

16 posts, Page 2 of 2

««1 2

Generate DDL script to create table and all constraints

Rate Topic

Display Mode

Topic Options

Author

Message

VPSD

Posted Wednesday, March 21, 2012 10:14 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, May 18, 2013 9:51 AM
Points: 12, Visits: 132

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

Richard Norris

Posted Wednesday, March 21, 2012 10:52 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, February 21, 2013 8:03 AM
Points: 264, Visits: 181

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

VPSD

Posted Wednesday, March 21, 2012 12:03 PM

Grasshopper

Group: General Forum Members
Last Login: Saturday, May 18, 2013 9:51 AM
Points: 12, Visits: 132

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

Post #1270407

Sailor

Posted Monday, October 01, 2012 11:48 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:09 AM
Points: 236, Visits: 746

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

VPSD

Posted Monday, October 01, 2012 12:25 PM

Grasshopper

Group: General Forum Members
Last Login: Saturday, May 18, 2013 9:51 AM
Points: 12, Visits: 132

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

Post #1366699

Sailor

Posted Monday, October 08, 2012 8:06 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:09 AM
Points: 236, Visits: 746

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 »

16 posts, Page 2 of 2

««1 2

Permissions