CODEWITHSUNDEEP
visual-studio-2013database-projectsql-server-2014
Metaphor
Metaphor

Reputation: 6395

How to add a new schema in a Visual Studio 2013 Database Project

I need to create an additional schema with some database objects in it within a Visual Studio 2013 Database Project. How can I do this?

Upvotes: 4

Views: 6452

Answers (2)

Tahir Alvi
Tahir Alvi

Reputation: 994

To add a new Schema into a database project in Visual Studio, you need to follow below steps.

Create a schema

  • Right-click the database project in Solution Explorer
  • Select Add --> New Item
  • In the next window, select Security on the left-hand side (Installed --> SQL Server --> Security)
  • In the list of file types, select Schema
  • Give the schema a name, then click Add.

This will now create a new file with a CREATE SCHEMA T-SQL statement in it.

Create table in a non-default schema

If you want to create a table belonging to this new schema, and follow the recommended folder structure, do the following:

  • Create a folder myschema (where 'myschema' is whatever you called your schema)
  • Create a folder Tables (and/or Views, Stored Procedures etc) under that folder
  • Right-click that last folder, select Add --> Table
  • As the name of the table, use the schema as a prefix: myschema.MyNewTable.sql.

This will create a file in the proper folder structure and in the proper schema. If the schema is misspelled or non-existing, you'll get an error telling you so.

Move existing tables to a different schema

This approach works in newer versions of Visual Studio:

  • Open the *.sql file containing a table definition
  • Right-click the name of the table
  • Select Refactor --> Move to schema
  • Select the new schema in the dropdown box
  • Check the global refactoring and accept the changes
  • Fix by hand any code, like sql-as-string or dynamic sql that references the moved table.

Upvotes: 6

Keith
Keith

Reputation: 21224

Add a new item to your project of type Schema (listed under SQL Server > Security).

For the objects that belong to this schema, add them as you normally would to your project. However they will default to the dbo schema so you will have to change that in each object's script (for example, CREATE TABLE dbo.Table1 becomes CREATE TABLE Schema1.Table1)

Upvotes: 10

Related Questions