Sql-server – SQL Server Database Diagram in SQL Management Studio creating multiple schemas

database-designschemasql-server-2008

I'm using a multiple schema approach (Multi-Tenant arhitecture) in the same DB and it's working great. I can use EF bounded contexts and split domains appropriately.

According to MSDN, multi-tenant architecture can be a shared server
(separate dbs), shared database (separate schema), or shared schema
architecture.

Currently i'm writing TSQL to get the DB design i want but I would like to know if there is a way to choose the schema before creating a new table when using Database Diagram tool in SQL Management Studio?

Best Answer

Database Diagrams is one of three Visual database tools.

After reading trough Visual Database Tools F1 Help, where there is no mention of choosing schema before creating a new table, which confirms that it automatically creates the table in the dbo schema.

To overcome this and still use Database Diagrams open your Table Properties window.

  1. Create new schema:

enter image description here

  1. Name your schema, define permissions etc.

enter image description here

  1. Change schema for your table while in design view using Properties window.

enter image description here

For me this approach is still faster that writing scripts, i didn't change to different designer tool and it works.

Hope it helps somebody.

Related Question