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.
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/.
In the use guide you'll find many examples : https://github.com/microsoft/mssql-scripter/blob/dev/doc/usage_guide.md, 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 :
export only schema and not data, by default only schema is exported- keep only the create statements, do not export drop statements
--script-create
Script object CREATE statements.
do not generate object permissions
--object-permissions Generate object-level permissions.
- 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.
- generate script for dependent objects
--include-dependencies
Generate script for the dependent objects for each
object scripted.
- 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.
