create table script generation

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » create table script generation

create table script generation

Rate Topic

Display Mode

Topic Options

Author

Message

Krishna1

Posted Wednesday, January 30, 2013 5:32 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:51 AM
Points: 127, Visits: 277

Dear All

I have created table with column constrains, default values. When I generate the script for this table from SSMS it gives the create table syntax and then alter table syntax for each constrain and spbind for defaults.

Is there a way to get it in the create table synatx itself?

Regards

Post #1413580

jeetsingh.cs

Posted Wednesday, January 30, 2013 5:47 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:06 PM
Points: 179, Visits: 380

As per my Findings no cause constraints are related to one another
if created earlier will cause the script to fail.

Post #1413587

anthony.green

Posted Wednesday, January 30, 2013 5:49 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Its the way that the create script works, so your asking to change the behavour of SQL, so that would need to be raised as a connect issue and they will investigate it, which will probably be closed off.

If you create your table like

CREATE TABLE
(
ID INT PRIMARY KEY,
Value INT DEFAULT(1),
...
...
)

And that is your prefered view, I would suggest saving the script to be able to run it again, otherwise you will be stuck with the way the the GUI wants to export the data you have asked it to.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1413591

Krishna1

Posted Wednesday, January 30, 2013 6:08 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:51 AM
Points: 127, Visits: 277

sorry jeetsingh.cs i did not understand you reply.

What i want is

CREATE TABLE [abc](
[Code] [CHAR](3) CONSTRAINT CK_abc_Code CHECK (UPPER(Code) LIKE '[A-Z][A-Z][A-Z]') NOT NULL,
[Description] [VARCHAR](40) NOT NULL,
[Rank] [INTEGER] CONSTRAINT CK_abc_Rankt CHECK (Rank > 0) NOT NULL,
CONSTRAINT [PK_abc_Code] PRIMARY KEY CLUSTERED (Code ASC)
) ON [PRIMARY]
GO

But what i get is

CREATE TABLE [dbo].[abc](
[Code] [char](3) NOT NULL,
[Description] [varchar](40) NOT NULL,
[Rank] [int] NOT NULL,
CONSTRAINT [PK_abc_Code] PRIMARY KEY CLUSTERED
(
[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[abc] WITH CHECK ADD CONSTRAINT [CK_abc_Code] CHECK ((upper([Code]) like '[A-Z][A-Z][A-Z]'))
GO
ALTER TABLE [dbo].[abc] CHECK CONSTRAINT [CK_abc_Code]
GO
ALTER TABLE [dbo].[abc] WITH CHECK ADD CONSTRAINT [CK_abc_Rankt] CHECK (([Rank]>(0)))
GO
ALTER TABLE [dbo].[abc] CHECK CONSTRAINT [CK_abc_Rankt]
GO

Post #1413596

anthony.green

Posted Wednesday, January 30, 2013 6:17 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Thats the way the GUI works and would be a change to the GUI which would be evaluated if you raised it as a Connect issue, but the likely hood of them actually doing it is slim.

If you want the script the way you executed it, then you need to ensure that you save the script so that you can run it the way you write it over and over, as SQL will always generate it the way you dont want it generating.

Post #1413601

jeetsingh.cs

Posted Thursday, January 31, 2013 6:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 11:06 PM
Points: 179, Visits: 380

hey this is how it works.
what are your issues with this type of script generation as it is works well
for the object creation.

Post #1414056

OTF

Posted Thursday, January 31, 2013 9:24 AM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:56 AM
Points: 254, Visits: 3,711

If scripting your objects a particular way is really that important to you, I guess you can check to see what scripting options are available to you with the smo api.

That being said though, it hardly seems worth it.

Post #1414217

Lowell

Posted Thursday, January 31, 2013 11:18 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:11 PM
Points: 11,605, Visits: 27,645

if you've gotta do it via TSQL, instead of stepping out via SMO to get it , i've spent a lot of time refining a few stored procedures to do that via TSQL.

sp_GetDDLa_Latest.txt returns a multi row table with the definitiion of any table, temp table, proc function or trigger.
sp_GetDDL_Latest.txt returns a single varchar(max) with the definitiion of any table, temp table, proc function or trigger.

both methods serve differnet functions; the first makes it REALLY readable.
the second is great when you want to stuff a CREATE TABLe definition into a field when auditing your scripts.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1414263

« Prev Topic | Next Topic »

Permissions