Quick answer: To export a database schema in SQL Server, open SQL Server Management Studio (SSMS), right-click the database, and select Tasks > Generate Scripts. Choose the database objects, open Advanced, set Types of data to script to Schema only, and save the script as a .sql file. This exports the database structure without its records.

Need to recreate your SQL Server database structure on another server? Exporting its schema can save you from rebuilding every database object manually.

The SQL Server database schema helps you create a development or testing environment, document database structures, or share database design without exposing sensitive records.

For this, SQL Server Management Studio provides a built-in Generate Scripts wizard that exports an entire database or selected objects as a SQL Script. You can also use SQL queries to retrieve table and column details, save metadata in Excel or CSV, or extract a schema-only DACPAC using Microsoft’s free SqlPackage utility.

Here, we will explain practical methods to export a database schema in SQL Server.

What Does a SQL Server Database Schema Include?

A database schema defines how a database organizes and manages its objects. It describes the database structures, such as relationships and rules that govern data storage. A SQL Server database schema can include several objects, which you can check out in the table below:

Tables Columns
Data types Primary Keys
Foreign Keys Indexes Constraints
Views Triggers
Stored procedures and functions
Important to Know: Only table definitions may not include every object or dependency. Therefore, carefully choose the objects and scripting options for the database recreation.

Types of SQL Server Database Schema Exports: Script, Data, and Diagram

SQL Server provides different ways to save database information, and each of them serves a different purpose. So identify the output type before your export.

Export Options What It Contains? Best For
Schema-only SQL script Definitions of selected database objects. Recreation of database structure without records.
Schema-and-data SQL script Object definitions and data-related SQL statements. Database recreation structure along with records.
DACPAC Database schema model (data is excluded by default). Schema deployment, comparison, and development workflows.
BACPAC Database schema and table data. Moving a database with its data.
Excel/CSV Tabular metadata, such as table names and column details. Database documentation and analysis.
Database Diagram Visual representation of tables and their relationships. Understanding or sharing database design.
Note: A database diagram is not an executable SQL script, and an Excel file containing table and column information is not a complete database backup. Similarly, a DACPAC and a BACPAC serve different purposes. For database recreation, a schema-only SQL script or a schema-only DACPAC is more appropriate.

5 Ways to Export SQL Database Schema Manually

Go through this section to learn the most appropriate solutions for exporting a SQL database schema. Try them out to find the applicable method based on your needs.

Method 1. Use SSMS to Export Database Schema In SQL Server

You can use SQL Server Management Studio to generate Transact-SQL scripts for an entire database or selected objects. Here are the steps to export SQL database schema without data:

Step 1. Open SQL Server Management Studio and Connect to Server.

Step 2. Expand Databases to select the required one.

Step 3. Right-click on the database and go to Tasks > Generate Scripts.Right click database go to tasks then click generate scripts

Step 4. Click Next on the Introduction page.

Step 5. Choose Objects from the following options:

  • Script entire database and all database objects
  • Select specific database objects

Once done, click Next to continue.Select database objects then click next

Step 6. Select the Save as script file and its associated options. Then, go to the Advanced section.Select save as script file

Step 7. Locate the Types of data to script option.

Step 8. Choose Schema only from the drop-down menu and click OK. Then, click Next.Choose schema only option

Step 9. Review your selections and proceed to Next.

Step 10. Wait for the saving process to complete and click Finish.

Note: You can Open or Save Report if needed.

Saving is completed then click finish

Method 2. Export SQL Database Schema of Selected Tables or Database Objects

You do not always need to export the entire database. SSMS lets you generate scripts for the tables and objects through the Script As menu in Object Explorer. Here are the steps to export SQL Server tables or other objects.

Export Schema of a Single Table or Object from SSMS

Follow the given steps to export single objects from SSMS with ease:

Step 1. Launch SSMS and Connect to the required SQL Server instance.

Step 2. Expand Databases and select your database.

Step 3. Expand the desired Tables folder and right-click on it.

Step 4. Choose the Script Table as > CREATE To.

Step 5. Select the New Query Editor Window option.

Step 6. SSMS generates the table’s CREATE TABLE statement in a new query window.

Step 7. Review the script and select File > Save As.

Step 8. Save the file with a .sql extension.

Export Schema of Multiple Tables Using Object Explorer Details

You can use Object Explorer Details to select multiple objects of the same category:

Step 1. Expand the required object folder.

Step 2. Press F7 or go to View > Object Explorer Details.

Step 3. Select the required objects while holding Ctrl.

Step 4. Right-click the selected objects.

Step 5. Choose the relevant Script As option and select the desired output location.

Important limitation: A CREATE TABLE script does not necessarily include every related database object. If you need foreign keys, triggers, indexes, views, stored procedures, or other dependencies, verify that they are included in your export.

Method 3. Run A SQL Query to Export Database Schema From MSSQL

You can use the SQL query to create a list of every table and column in a database. Follow the given steps to export data from SQL database to Excel or CSV.

Step 1. Select your database from the Available Databases list.

Step 2. Choose the New Query option and paste the provided query.

SELECT
s.name AS SchemaName,
t.name AS TableName,
c.column_id AS ColumnPosition,
c.name AS ColumnName,
ty.name AS DataType,
CASE
WHEN c.max_length = -1 THEN ‘MAX’
WHEN ty.name IN (‘nchar’, ‘nvarchar’)
THEN CAST(c.max_length / 2 AS VARCHAR(10))
WHEN ty.name IN (‘char’, ‘varchar’, ‘binary’, ‘varbinary’)
THEN CAST(c.max_length AS VARCHAR(10))
ELSE NULL
END AS MaximumLength,
c.precision AS NumericPrecision,
c.scale AS NumericScale,
CASE
WHEN c.is_nullable = 1 THEN ‘YES’
ELSE ‘NO’
END AS IsNullable,
c.is_identity AS IsIdentity
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.schema_id = s.schema_id
INNER JOIN sys.columns AS c
ON t.object_id = c.object_id
INNER JOIN sys.types AS ty
ON c.user_type_id = ty.user_type_id
WHERE t.is_ms_shipped = 0
ORDER BY
s.name,
t.name,
c.column_id;

Step 3. Select Execute or press F5. The results appear in the grid.

Step 4. Review the results. A MaximumLength of -1 means MAX, and an empty value means the data type has no character length.

Step 5. To include column names in the saved file, go to Tools > Options > Query Results > SQL Server > Results to Grid, check Include column headers when copying or saving the results, and select OK. Restart SSMS or open a new query window.

Step 6. Right-click anywhere in the results grid to Save Results As > CSV > Save.

Step 7. Add the line below before ORDER BY to export only specific tables.

WHERE TABLE_SCHEMA = ‘dbo’ AND TABLE_NAME IN (‘Customers’, ‘Orders’)

Make sure to replace the schema and table names with yours.

Method 4. Create and Save Database Diagrams In SSMS

You can create and export database diagrams in SSMS with the help of the following steps:

Note: As per Microsoft, only the owner of a diagram, or a member of the db_owner role, can open it.

Step 1. Go to Object Explorer, expand Databases, and select the required one.

Step 2. Right-click on Database Diagrams and choose the New Database Diagram option.

Note: If SSMS says the database doesn’t have the support objects required for diagramming and asks whether to create them, select Yes.

Right click database diagrams and click new database diagram
Step 3. Select the required tables in the Add Table wizard.

Step 4. Click the Add option to load tables. Then, tap Close.Click add option

Step 5. Review the diagram. Each box is a table, and the lines show the relationships between tables. Drag the tables to arrange them so the lines are easy to follow.

Step 6. Right-click the table and select Table View > Keys to change how much detail a table shows.

Step 7. Select File > Save Diagram, enter a name, and select OK.

Note: SSMS doesn’t post changes you make to a diagram to the database until you save it. If SSMS says tables will be saved to the database and you didn’t mean to change any, select No and check what you changed.

Step 8. Open the created diagram and right-click on the blank area.

Step 9. Choose the Copy Diagram to Clipboard option.

Step 10. Paste the image into Paint, Word, or PowerPoint. Then, save the file as an image.

Method 5. Export SQL Database Schema Using SqlPackage

A DACPAC is a database deployment package that contains a model of the database schema. By default, the SqlPackage Extract action does not include table data. Here are the steps to export database schema in SQL Server using it:

Step 1. Download and install SqlPackage from Microsoft’s official site.

Step 2. Open the Command Prompt, Terminal or, another supported command-line interpreter.

Step 3. Run the below-provided extract command.

SqlPackage /Action:Extract \
/TargetFile:”C:\Export\DatabaseSchema.dacpac” \
/SourceServerName:”YourServerName” \
/SourceDatabaseName:”YourDatabaseName” \
/SourceUser:”YourUsername” \
/SourcePassword:”YourPassword” \
/p:ExtractAllTableData=false

Step 4. Wait for SqlPackage to finish. It displays progress and a result message.

Step 5. Check the result by verifying that the .dacpac file exists in your folder.

Important to Know: A DACPAC is not the same as a .sql script. It is a structured package intended for database development and deployment workflows.

DACPAC vs. BACPAC: What is the Difference?

Although both formats package SQL Server database information, they serve different purposes. Here’s a table to clarify the difference between them.

Feature DACPAC BACPAC
File Extension .dacpac .bacpac
Database Schema Included Included
Table Data Excluded by default. Included
Primary Purpose Schema deployment & comparison. Database portability with schema & data.
Application Development & deployment workflows. Moving a database with its records.
Author’s Verdict: Choose a DACPAC when you need a database schema package, and a BACPAC for exporting the database schema along with its table data.

Conclusion

Here, we have covered a detailed guide to export database schema in SQL Server. You can check the different available methods to export a schema based on your requirements. For example, generate scripts works for schema-only .sqlfile, a DACPAC suits deployment, all these methods need database to be accessible. But, if your database becomes corrupted, damaged, or inaccessible, these methods may not work. In that situation, Kernel for SQL Database Recovery tool can help recover tables, triggers, rules, functions, stored procedures, primary keys, foreign keys, and other database objects in their original structure. Whichever method you use, make sure to verify the exported schema before using it on another server.

Related Posts
Google Trust