October 15, 2001 at 7:49 am
One question, through EM I can script tables and have the primary key
constraint as a separate piece of sql, but cannot seem to get the correct
value to use for SCRIPTTYPE. Currently I am using the following. It works,
but adds the PKC inline, which I would like to avoid if possible.
oTBLDDL.Script 256 + 73736 + 2 + 64 + 4, "\\cssfiler01\sqladmin$\dba\" &
oDatabase.Name & "_TBL_DDL.sql"
Any thoughts/sugestions? Thanks in advance.
Be great!
Michael
October 15, 2001 at 5:46 pm
Thanks for posting Michael. Just because Im curious, why do you not want the primary key script inline? Also - using numbers instead of the constants really makes for hard to read code, took me a while paging through all of them to see what you were doing. I've included constants in the code here so readers can compare.
Here is what I came up with:
Dim oserver As SQLDMO.SQLServer
Dim oTable As SQLDMO.Table
Set oserver = New SQLDMO.SQLServer
oserver.LoginSecure = True
oserver.Connect "."
Set oTable = oserver.Databases("Northwind").Tables("Categories")
oTable.Script SQLDMOScript_NoDRI + SQLDMOScript_AppendToFile + SQLDMOScript_ObjectPermissions + SQLDMOScript_ToFileOnly + SQLDMOScript_Default, "C:\testscript.sql"
oTable.Script SQLDMOScript_AppendToFile + SQLDMOScript_DRI_PrimaryKey + SQLDMOScript_Indexes, "C:\testscript.sql"
Set oTable = Nothing
oserver.DisConnect
Set oserver = Nothing
Running this against Northwind (hard coded that way!) results in the following:
CREATE TABLE [Categories] (
[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
[CategoryName] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Description] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Picture] [image] NULL ,
[rowguid] uniqueidentifier ROWGUIDCOL NOT NULL ,
[TestCol] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
GRANT SELECT ON [dbo].[Categories] TO [public]
GO
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[Categories] TO [TEST8]
GO
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[Categories] TO [TEST12]
GO
GRANT INSERT ON [dbo].[Categories] TO [A1]
GO
GRANT INSERT ON [dbo].[Categories] TO [A2]
GO
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[Categories] TO [TestUser]
GO
GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[Categories] TO [TestUserSQL]
GO
ALTER TABLE [Categories] ADD
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[CategoryID]
) ON [PRIMARY]
GO
CREATE INDEX [CategoryName] ON [Categories]([CategoryName]) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [index_2041058307] ON [Categories]([rowguid]) ON [PRIMARY]
GO
Will that do it?
Andy
October 15, 2001 at 7:57 pm
Perfect! Sorry for the abbreviated code. My full script has the values with comments. I used the numeric values because that is what I found in an example. It worked so I left it.
As far as the seperate PK, it is because persons other than myself may use the output to backout structure changes - long story. I was trying to "bullet proof" the process and avoid someone re-inserting data with the PKC already on the table.
Again, thanks for taking the time to assist. I will incorporate the named values for better readability.
Be great!
Michael
October 16, 2001 at 4:50 am
Gotcha. Thanks for the follow up.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply