January 30, 2013 at 5:32 am
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
January 30, 2013 at 5:47 am
As per my Findings no cause constraints are related to one another
if created earlier will cause the script to fail.
January 30, 2013 at 5:49 am
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.
January 30, 2013 at 6:08 am
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
January 30, 2013 at 6:17 am
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.
January 31, 2013 at 6:09 am
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.
January 31, 2013 at 9:24 am
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.
January 31, 2013 at 11:18 am
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply