Lesson Learned #464: Utilizing SMO's Scripting Option in Azure SQL Database.

Today, I encountered a unique service request from a customer inquiring about alternative methods to script out a table and all its dependencies in Azure SQL Database. Traditionally, several approaches are employed, such as utilizing stored procedures like sp_help, sp_depends, or functions like object_definitionor SSMS GUI. These methods, while useful, but, I would like to share other options using SQL Server Management Objects (SMO).

Script:

# Define connection details
$serverName = "servername.database.windows.net"
$databaseName = "DBName"
$tableName = "Table1"
$schemaName = "dbo" # Update if using a different schema
$userId = "UserName"
$password = "Pwd!"

# Create a Server object
$serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($serverName, $userId, $password)
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($serverConnection)

# Access the database
$database = $server.Databases.Item($databaseName)

# Access the specific table
$table = $database.Tables.Item($tableName, $schemaName)

# Configure scripting options
$scripter = New-Object Microsoft.SqlServer.Management.Smo.Scripter($server)
$scripter.Options.ScriptSchema = $true
$scripter.Options.Indexes = $true
$scripter.Options.Triggers = $true
$scripter.Options.ScriptDrops = $false
$scripter.Options.WithDependencies = $false

# Script the table
$scripter.Script($table) | ForEach-Object { Write-Output $_ }

Running this small PowerShell Script we are going to have the structure of the table including triggers and Indexes. 

Example:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[PerformanceVarcharNVarchar](
	[Id] [int] NOT NULL,
	[TextToSearch] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
 CONSTRAINT [PK_PerformanceVarcharNVarchar] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

SET ANSI_PADDING ON

CREATE NONCLUSTERED INDEX [PerformanceVarcharNVarchar1] ON [dbo].[PerformanceVarcharNVarchar]
(
	[TextToSearch] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF)