Creating a SQL Server Database Project in Visual Studio 2012

I used to create my application database by managing a series of install scripts with one table or stored procedure per file.  In those files I had to manage potential changes to those schema items and also handle conditional creation of those entities if they did not exist.  SQL Server Data Tools built into Visual Studio 2012 has made this into a much simpler process.  A version of this existed in Visual Studio 2010, but it is even better in Visual Studio 2012.

Conditional Creation of Tables and Columns (The Old Way)

If your application has numerous deployment versions and you want your script to adapt and be able to either install in a fresh database or into a prior version of the schema, then you have lots of work to do.  First you create the latest version of the table, if it does not exist.  If it does already exist, then you check for the missing columns added since your initial deployment and add them as needed.  You may also need to drop columns that have been removed over time.  Here is an example of an old script where I have done this.

Please excuse the name prefixes. I used to prefix table and stored procedure names with a prefix indicating a kind of namespace. I’ve since changed over to using different schemas. This script also checks for an existing table with sysobjects since this was written for SQL Server 2000 and then updated to use SQL server 2005. It has since been replaced, however sysobjects still works for backwards compatibility. I discourage you from using this in any current or future projects.

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XQSXG_MMS_MailMessage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[XQSXG_MMS_MailMessage](
	[MailMessageID] [int] IDENTITY(1,1) NOT NULL,
	[MailMessageTypeID] [int] NULL,
	[ToAddress] [nvarchar](256) NOT NULL,
	[ToUser] uniqueidentifier NULL,
	[Priority] [int] NOT NULL CONSTRAINT [DF_XQSXG_MMS_MailMessage_Priority]  DEFAULT ((0)),
	[Subject] [nvarchar](512) NOT NULL,
	[Format] [int] NOT NULL CONSTRAINT [DF_XQSXG_MMS_MailMessage_Format]  DEFAULT ((0)),
	[Body] nvarchar(max) NULL, /*SQL Server 2005+, prior used NText*/
	[CreatedDate] [datetime] NOT NULL CONSTRAINT [DF_XQSXG_MMS_MailMessage_CreatedDate]  DEFAULT (getutcdate()),
	[Attempts] [int] NOT NULL CONSTRAINT [DF_XQSXG_MMS_MailMessage_Attempts]  DEFAULT ((0)),
	[LastAttemptDate] [datetime] NULL,
	[CompletedDate] [datetime] NULL

if not exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'XQSXG_MMS_MailMessage' and COLUMN_NAME = 'MailMessageTypeID')
	MailMessageTypeID int NULL
if not exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'XQSXG_MMS_MailMessage' and COLUMN_NAME = 'ToUser')
	ToUser uniqueidentifier NULL
if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'XQSXG_MMS_MailMessage' and COLUMN_NAME = 'FromAddress')
	ALTER TABLE dbo.XQSXG_MMS_MailMessage Drop Column FromAddress
if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'XQSXG_MMS_MailMessage' and COLUMN_NAME = 'CCAddress')
	ALTER TABLE dbo.XQSXG_MMS_MailMessage Drop Column CCAddress
if exists(select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'XQSXG_MMS_MailMessage' and COLUMN_NAME = 'BCCAddress')
	ALTER TABLE dbo.XQSXG_MMS_MailMessage Drop Column BCCAddress

if not exists 
(	select * from dbo.sysobjects where id = object_id(N'[dbo].[PK_XQSXG_MMS_MailMessage]') and parent_obj = 
	(select id from dbo.sysobjects where id = object_id(N'[dbo].[XQSXG_MMS_MailMessage]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

Creating a Database Project in Visual Studio 2012 (The New Way)

You can reverse engineer a database project from an existing database, or create a new project from scratch.  This article will focus on how to create one from scratch.  Open Visual Studio and either create a new solution or open an existing one to which this new database should be a part of.

Adding the Project to a Solution

Select the Other Languages, SQL Server template group on the left.  Then choose the SQL Server Database Project type.  Enter a project name and press OK.  I usually pick a project name matching the class library that will contain the business layer or data layer that will interact with this database, and then append Database to the end of that name.  It may make more sense to also put SQL in the name; just in case you use another type of database in the future.


Update the Project Properties

You should check out the project properties and see what options are available.  On the main Project Settings tab page, there is a ‘Database Settings’ button that lets you specify any metadata to be applied to the database as a whole.  The defaults have worked for me, but if you need a specific database collation, file group, or need certain flags like ANSI_PADDING then check that out.

I tend to override the default output type, by checking the ‘Create script (.sql file)” option as shown here.  I do not change the default schema from ‘dbo’; even knowing that below I want most of my tables, functions, and procedures in a specific schema.


Import a Database (Optional)

If you already have a database to start with; you can import from the current schema.  Then you can follow the other sections below for making changes and publishing those changes.  To import a schema, just right click on the project node in solution explorer and select menu “Import” -> “Database”.  Then configure the database connection and pick the options for things you want to import.


I prefer the Folder structure of “Schema\Object Type”.  This is what I will assume for the following sections; It is also the default selection for the Import dialog.   I don’t normally change any of the import setting defaults.  If you need permissions of any specific database settings from your existing database then select those import options.  You can modify the database settings in the project properties as noted in the previous section.

Creating a Schema

Before I create any tables, I usually define a schema in which I will place all my database objects for this project.  This allows you to have simpler names for your tables, since the schema scopes them similar to a namespace in .Net code.

It may not really matter where you put the schema file, however I follow the convention used when reverse engineering a database.  Create a folder in the database project of the same name that you will name your schema.  Then add the schema file to that folder using that same name.


This seems so much better to me than the old way above where I showed a prefix on a table name to facilitate grouping of related tables.  Having different schemas for loosely coupled or unrelated sets of tables also helps me think of ways tables could be segmented into different database shards.  You can either go the route of one database project per schema, or one database project for all your schemas.  I usually make that decision based on how I want to deploy the database.  One database project equals a deployment to one database instance.

Add a Table

When reverse engineering a database into a database project it creates folders under the schema folder for Tables, Functions, and Stored Procedures.  I follow the same convention when creating these items manually.  I just create a table by right clicking on the Tables folder under the schema and selecting the ‘Add Table’ menu item.

The New Item dialog with table selected.
Solution Explorer after adding a schema file and tables

Table Designer Overview

The table designer gives you options as to how you want to design your table.  It has a design pane which has a columns grid and keys overview with right click support for adding new keys.  It also has a raw text pane with the sql required to create the table as defined in the design pane.  As you type in the raw text pane the changes appear in the design pane; and as you change details on the design pane it updates the raw text pane.  On my 5 year old laptop, I have not experienced any performance issues either to open the file or in having updates sync between the panes.


As you use this designer all the keys and constraints are added in the table definition sql file.

Deploying the Database – Publish

Publishing the database changes is very simple.  Just right click on the database project in solution explorer, and select “Publish”.  A dialog appears for connection details.


Assuming you followed the steps above during project setup, this will just generate a script file.  I prefer script files so that I have them ready for promotion to the next environment.  If your project properties default to do an automatic publish instead of generating a script, you can override this by just pressing the ‘Generate Script’ button.


Deploying the Database – Schema Compare

You can also create a deployment script with the schema compare command.  This is also available as a right click menu item on the database project node in solution explorer.  This gives you more flexibility.  You pick the database target to compare the database project to and it tells you what is changed.  Then you can choose which items are included in the generated script.  If you leave all changes selected, then this generates the same script as if you followed the ‘Publish’ option in the previous section.

This comparison shows everything as a difference since the schema has not been deployed yet.

To begin a comparison to a database, pick in the ‘Select Target’ dropdown to select a database connection.  In the screenshot above that dropdown has my connection name “.\SQLExpress.CandorMail”.  Then press the Compare button (Or use shortcut Shift-Alt-C) to see the changes.  If you have changes, then press the “generate script button” (Or use shortcut Shift-Alt-G).

If you actually want to deploy these changes to the target database now, then press the ‘Update Target” button (next to generate script).  This has no shortcut, thankfully.  I personally wouldn’t want a possible accidental key press of the wrong combination to publish a database change to a production database.

Build Errors

One of the great advantages of a database project is the continuous ‘compilation’ of the database project objects.  If you have invalid definitions or reference other objects that do not exist, then you will see compilation errors.  This is a great development enhancement over parsing scripts and manually running them against a local database instance on a regular basis.

As you type you will see problem areas highlighted in the raw text pane as shown here.  If you hover over it, you’ll see the error message.


Also if you view the errors list the detail will be shown.  If you double click on the error it will navigate you to the table designer where the error is located.

This error shows that the column name referenced by the foreign key is incorrect. It has a missing ‘s’ in the name.

Database References

You may have multiple database projects in your solution that have some level of dependency.  Maybe one of the projects is a set of customizations to a base database product defined in another project.  Or maybe you just want each schema defined in a separate project.

Without a reference the project that depends on external database objects will not compile (generate a script) if it cannot find the referenced database object.  To fix this you can create a reference to the other database project.  Just right click on the ‘references’ node of the database project and select “Add Database Reference”.  Then you can pick another database project in the solution, or a system database, or a dacpac file exported from another database.


This works on my machine.  I didn’t research if it works with less features installed than I have, but it probably does.  I have the following Microsoft development tools installed.

  1. Visual Studio 2012 Professional with update 1 (Full Install , lots of ‘features’.  Not all are listed below)
    • Now includes: SQL Server Data Tools 11.1.20627.00 (separate install for VS 2010)
    • Now includes: Microsoft SQL Server Data Tools (yes, this is different from the previous item)
  2. SQL Server 2012 Express with all features including SQL Server Management Studio (ENU\x64\SQLEXPRWT_x64_ENU.exe, 669.9 MB).  Obviously pick the 32 bit version instead if your OS is 32 bit.

About the Author

Michael Lang

Co-Founder and CTO of Watchdog Creative, business development, technology vision, and more since 2013, Developer, and Mentor since 1999. See the about page for more details.


  • By bga rework stations

    hi That’s a good post

  • By Arnold Gultom

    why I cannot connect SQL Server 2012 to VS 2012 Express for Web?? The Server Name is not visible. I have installed the SQL Server first then installed VS 2012.

  • By Alex Edelstein

    very helpful!

  • By Marcos Lapa

    Hello, great post!
    But I need your help…
    I’m trying to put a web reference to my SQL Server Database Project, but I don’t see how can I do this in Visual Studio 2012…
    I’m using the Visual Studio 2012 Ultimate version and the web reference option doesn’t appear in the Solution Explorer.
    Any idea?

    • mlang74 By mlang74

      I am unclear as to what you are trying to do? Either
      1) you are trying to reference the database project from a webservice project, and then query the database from the webservice.
      2) you are trying to reference the webservice project from the database project, and then call a webservice endpoint from within the database.

      1) If you want to query a database from within a webservice, you do not do that by referencing the database project. You don’t even need to have a database project to do this, which is a good thing. To call a sql database from within a webservice project, I recommend you just use a SqlConnection and SqlCommand to do so. Or use an ORM that abstracts away all the database calls for you. You can see an example of how to do this in Candor.Security.SqlProvider.UserProvider.

      2) A database stored procedure cannot call a webservice. You technically may be able to do so via a .Net CLR class inside a database, but that is not the kind of thing the Sql .Net CLR was meant for.

      Let me know if you meant something else in your question, or if you have any clarifying thoughts to help me answer your question better.

  • By Vilas Shende

    I not understood how to create new data base, because you guide us from your existing data base, so please let me know How can i create data base from new connection

  • By Ben Corpus

    Hi, Michael.

    I read your post “Creating a SQL Server Database Project in Visual Studio 2012” and I wanted to know how you maintain the database as it changes in its lifetime. For example, you show how to generate it from scratch (bootstrap), which is fine, but how to maintain it moving forward.

    Is there anything in VisualStudio 2012 that a developer could run to generate idempotent SQL scripts?

    • mlang74 By mlang74

      Yes, you can generate a script based on the state of an existing database. The section “Deploying the Database – Schema Compare” works not only on a fresh empty database, but also on a database at any prior version of your schema. When you press the “generate script” button per that section above, you will get a sql script ready to give your DBA or you can run it yourself, depending on how your process works.

      My process – as an example:
      Typically during development I will have a local database on my machine that I regularly publish changes to during database schema development and I test that until the functionality I am working on is complete. Then I have another test environment that integrates with other developers changes. I point the schema compare at that database and generate a script and then deploy from visual studio. Once all my changes are tested and a regression test is complete, I then schema compare against the staging database which duplicates the production schema. To ensure staging matches production, occasionally a production backup is restored into staging and then permissions are altered in staging as needed to allow for validation testing. I generate the script against the staging database and hand that script over for deployment by another team member. If that staging deployment goes smoothly and regression tests succeed, then I get ready for production deployment.

      production deployment: Run the schema compare now against production. Then check that the script matches the generated script for staging. Resolve that any differences are related to permissions, which should be the only difference in my process. Then at a scheduled time, someone backs up production and then runs the newly generated production script against production concurrently with corresponding application deployments.

      I hope this helps.

  • By JNeo

    Great post!
    In the “Deploying the Database – Schema Compare” section, you wrote “If you leave all changes selected, then this generates the same script as if you followed the ‘Publish’ option in the previous section.”, I think this is not true if you have pre or post-deployment scritpts because they run in case of the ‘Publish’ but don’t run using ‘Schema Compare’.

    • mlang74 By mlang74

      You are correct. I wasn’t considering pre and post since in my case I had nothing in those files.

  • By pankaj

    hello! thank you very much for your crystal clear explanation……just one query ….I am trying to create service based database….but when I try to select table from dataset configuration wizard I cant select ….though table radio button is present…I am using sql server 2012 and vs 2012….Please help…

    • mlang74 By mlang74

      Creating your database is beyond the scope of this article. to creata a new database I usually just open sql server management studio and use the simple commands available there.

  • By Namrata Jain

    Great post but i am not able to have different schema as prefix to table name like we have namespace for class name in c#. In my database project i need to divide my table module wise and want to add schema name to table name for example in commercial module name of table can [Commercial].[Sales] but here even after adding schema i am getting name as [dbo].[sales].

    please help. i think i am left out with some setting.

    • mlang74 By mlang74

      If you followed the instructions above and the schema for a new table is still dbo, then just edit the sql file for the table and update dbo to the intended schema. Its a one time operation for the table.

  • By Sasha

    Thanks for the great post. Is it possible somehow to run scripts generated in Visual Studio directly in SQL Server Management Studio? Thanks.

    • mlang74 By mlang74

      Yes. You have the option to either publish the database change or just to generate the script. Use the ‘generate script’ button to generate a script file. Then run it using your tool of choice.

  • By shankar

    Very Informative

  • By Vandergood

    great post thank’s lot perfect

  • By babyanne

    can i just ask, what will be the next step for me to be able to create my own database?

  • By Success

    Thanks very much

  • By Geo

    Some questions. If you have publish the new changes from DB project to an existing DB, how can we know which version/changeset is the latest in DB? I mean how do we know from the project which changes are available in the database.

    • mlang74 By mlang74

      You compare by following section above “Deploying the Database – Schema Compare”. Nothing is automatically going to tell you a changeset number. If you need that then enter a record in the database with the changeset number, as in part of the change script writes to a table of your design that stores a version number.

  • By ManojPhotography

    Reblogged this on Developer M.

  • By Raval nikul

    Nice your theory. Good…..