ScriptType Value?

  • Michael S. Armentrout

    Ten Centuries

    Points: 1178

    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

  • Andy Warren

    SSC Guru

    Points: 119676

    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

  • Michael S. Armentrout

    Ten Centuries

    Points: 1178

    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

  • Andy Warren

    SSC Guru

    Points: 119676

    Gotcha. Thanks for the follow up.

    Andy

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply