How to copy architecture of an existing database to a new database in SQL Server

Question

I'm using

Microsoft SQL Server 2012 (SP1) - 11.0.3153.0 (X64) Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

and I need to copy the database architecture(Tables and their constraints) to a new database so I can extract the database diagram. The current database won't allow me to(extract a diagram), but any new database I create, will.

I tried creating one with the old ones mdf but it threw an error.

I tried to do an export, which copied all of the tables, but none of the constraints!!!

Answer 1

In SQL Server Management Studio, right click on your database, select Tasks, Generate Scripts. Select the options for generating tables, indexes and constraints. Use the generated script(s) to recreate a new database where you can create the diagrams.

Answer 2

1. Management studio->Right click on the database
2. Task->Generate script->Script entire database and all database objects
3. generate the script to a file/clipboard/new query window
4. Create new database schema from this script

Answer 3

If you have access to (friendly) developer they may have a copy of MS Visual Studio with SQL Server data tools installed; in it there is a handy feature called SQL Schema Compare that can copy whole or parts of schemas between databases.