Generate DDL for a Microsoft SQL database

Introduction

Summary: This page explains how to generate DDL for a Microsoft SQL database using SQL Server Management Studio and how to generate DDL for Microsoft Azure SQL database using MySQL-scripter for use with the SQL Analyzer extension. 

The official Microsoft documentation for scripting databases using the SQL Server Management Studio is here: https://docs.microsoft.com/en-us/sql/ssms/tutorials/scripting-ssms?view=sql-server-2017#script-databases external link.

Extraction examples

DDL extraction for a specific Microsoft SQL database

Connect to a server that’s running Microsoft SQL Server, expand the Databases node and then right-click Your Database > Tasks > Generate Scripts:

Select Next to open the Choose Objects page:

Select Script entire database and all database objects, then select Next:

Save scripts to a specific location, and click Advanced:

Ensure the Advanced options are as follows:

Select OK and click Next on the Summary page:

Finally click Finish to generate the script:

How to extract Microsoft Azure SQL DB

You generate DDL scripts by using mssql-scripter, see https://azure.microsoft.com/en-us/updates/mssqlscripter/ external link.

In the use guide you’ll find many examples : https://github.com/microsoft/mssql-scripter/blob/dev/doc/usage_guide.md external link, one of them shows how to script all to a file:

## script all the data to a file.
mssql-scripter -S localhost -d AdventureWorks -U sa --script-create --object-permissions --include-dependencies --exclude-headers --exclude-defaults --exclude-extended-properties > ./adventureworks-data.sql

The recommendation is to :

  • (tick) export only schema and not data, by default only schema is exported
  • (tick) keep only the create statements, do not export drop statements

–script-create

Script object CREATE statements.

  • (minus) do not generate object permissions

–object-permissions Generate object-level permissions.

  • (minus) do not check if object exist

–check-for-existence
Check that an object with the given name exists before
dropping or altering or that an object with the given
name does not exist before creating.

  • (tick) generate script for dependent objects

–include-dependencies
Generate script for the dependent objects for each
object scripted.

  • (tick) exclude headers, defaults and extended properties

–exclude-headers Exclude descriptive headers for each object scripted.

–exclude-defaults Do not script the default values.
–exclude-extended-properties Exclude extended properties for each object scripted.