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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy