Export database schema into SQL file

sql sql-server database tsql export
seeker picture seeker · Aug 20, 2012 · Viewed 182k times · Source

Is it possible in MS SQL Server 2008 to export database structure into a T-SQL file?

I want to export not only tables schema but also primary keys, foreign keys, constraints, indexes, stored procedures, user defined types/functions.

Also I don't want the data to be present in this T-SQL file.

Is there any way to achieve that?

Answer

Taryn picture Taryn · Aug 20, 2012

You can generate scripts to a file via SQL Server Management Studio, here are the steps:

  1. Right click the database you want to generate scripts for (not the table) and select tasks - generate scripts
  2. Next, select the requested table/tables, views, stored procedures, etc (from select specific database objects)
  3. Click advanced - select the types of data to script
  4. Click Next and finish

MSDN Generate Scripts

When generating the scripts, there is an area that will allow you to script, constraints, keys, etc. From SQL Server 2008 R2 there is an Advanced Option under scripting:

enter image description here

Related questions

Altering column size in SQL Server

How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5)

Read More
Need to list all triggers in SQL Server database with table name and table's schema

I need to list all triggers in SQL Server database with table name and table's schema. I'm almost there with this: SELECT trigger_name = name, trigger_owner = USER_NAME(uid),table_schema = , table_name = OBJECT_NAME(parent_obj), isupdate = OBJECTPROPERTY( …

Read More
SQL Server: Get data for only the past year

I am writing a query in which I have to get the data for only the last year. What is the best way to do this? SELECT ... FROM ... WHERE date > '8/27/2007 12:00:00 AM'

Read More