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!