How to replicate the "Script Table As... Drop And Create..." SQL Server Management Studio action in SMO?

50 Views Asked by At

I am looking to automate script generation for versioning purposes for our SQL Server Database by using the C# Microsoft.SqlServer.Management.Smo package. I am fiddling with the ScriptingOptions class to try to get tables scripting exactly how you would right-click, script table as, drop and create. I do not seem to be able to get the settings to match quite how SSMS does it (which is how we have been manually doing it so far). I am struggling to find posts online or documentation on what the ScriptingOptions class properties do.

I think the key parts are the USE database, full schema names, Drop Constraints, drop table if exists, create table with primary key, and adding the defaults. I know it will generate pieces of this that may need to be combined via string concatenation, but some I haven't gotten at all.

An abridged example from SSMS:

USE <databasename>

ATLER TABLE <table> DROP CONSTRAINT <constraint1>

ATLER TABLE <table> DROP CONSTRAINT <constraint2>

...

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].<table>') AND type in (N'U'))

DROP TABLE [dbo].[<table>]

GO

/****** Object: Table [dbo].[ACCOUNT] Script Date: 2/26/2024 12:50:43 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[<table>](

...

PRIMARY KEY CLUSTERED...

ALTER TABLE <table> ADD DEFAULT...

etc.

The ScriptingOptions class properties I am working with so far are the following:

{
    EnforceScriptingOptions = true,
    IncludeDatabaseContext = true,
    AllowSystemObjects = false,
    SchemaQualify = true,
    DriAllConstraints = true,
    Indexes = true,
    DriDefaults = true,
    NoCollation = true
}

Edit: Here is my output as is:

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[<table>](
    <columns>...
PRIMARY KEY CLUSTERED
(
        [<column1>] 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
CREATE NONCLUSTERED INDEX [IDX_ACCOUNT_CLOSEDATE] ON [dbo].[<table>]
(
        [<column1>] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[<table>] ADD  DEFAULT (' ') FOR [<column1>]
GO
...
<more ADD DEFAULTS>

OR is there a better way to automate this? Looking to script tables, views, functions, and stored procedures.

Thanks!

0

There are 0 best solutions below