title: SQL Server Database Project in Visual Studio - how to structure for new database
body: <p>I want to create a SQL Server database project where a new database is created. Here is the structure of my project :</p>
  
  <p><a href="https://i.stack.imgur.com/conLL.png" rel="nofollow noreferrer">SQL Server Database Project Structure</a></p>
  
  <p>Basically, I want 4 tables, so I have added 4 <code>.sql</code> scripts for them.</p>
  
  <pre><code>CREATE TABLE [dbo].[MovieActors]
  (
      [Id] INT NOT NULL PRIMARY KEY,
      [ActorID] INT NOT NULL,
      [MovieID] INT NOT NULL
  )
  
  CREATE TABLE [dbo].[Actors]
  (
      [Id] INT NOT NULL PRIMARY KEY, 
      [Name] NVARCHAR(50) NOT NULL, 
      [Bio] NVARCHAR(MAX) NULL, 
      [Sex] NCHAR(10) NOT NULL, 
      [DOB] DATE NULL
  )
  
  CREATE TABLE [dbo].[Movies]
  (
      [Id] INT NOT NULL PRIMARY KEY, 
      [Name] NVARCHAR(50) NOT NULL, 
      [Year of Release] SMALLINT NULL, 
      [PLOT] NVARCHAR(MAX) NULL, 
      [Poster] NVARCHAR(MAX) NULL,
      [ProducerId] int FOREIGN KEY REFERENCES Producers(Id)
  )
  
  CREATE TABLE [dbo].[Producers]
  (
      [Id] INT NOT NULL PRIMARY KEY, 
      [Name] NVARCHAR(50) NOT NULL, 
      [Bio] NVARCHAR(MAX) NULL, 
      [Sex] NCHAR(10) NULL, 
      [DOB] DATE NULL
  )
  </code></pre>
  
  <p>And I have also added pre-deployment (for deleting existing data) and post-deployment scripts (for seeding initial data).</p>
  
  <p>Pre-Deployment:</p>
  
  <pre><code>DROP TABLE [dbo].MovieActors
  DROP TABLE [dbo].Movies
  DROP TABLE dbo.Actors
  DROP TABLE dbo.Producers
  </code></pre>
  
  <p>Post-Deployment: </p>
  
  <pre><code>INSERT INTO dbo.Actors (Id, Name, Sex)
  VALUES (1, 'Actor1', 'MALE')
  
  INSERT INTO dbo.Producers (Id, Name, Sex)
  VALUES (1, 'Producer1', 'MALE')
  
  INSERT INTO dbo.Movies (Id, Name, ProducerId)
  VALUES (1, 'Movie1', 1)
  
  INSERT INTO dbo.MovieActors (Id, MovieID, ActorID)
  VALUES (1, 1, 1)
  </code></pre>
  
  <ol>
  <li>But everytime I am building the project it does not get deployed - only showing build succeeded</li>
  <li>Moreover, where is the database creation script. </li>
  <li>When I right clicked on publish and chose option to generate script , only pre and post deployment scripts were in the main script.  - </li>
  </ol>
  
  <p><a href="https://i.stack.imgur.com/Pn8Vg.png" rel="nofollow noreferrer">Right Click Project -> Publish option</a></p>
  
  <p>This is the script that gets generated - </p>
  
  <pre><code>/*
  Deployment script for MoviesDatabase
  
  This code was generated by a tool.
  Changes to this file may cause incorrect behavior and will be lost if
  the code is regenerated.
  */
  
  GO
  SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;
  
  SET NUMERIC_ROUNDABORT OFF;
  
  
  GO
  :setvar DatabaseName "MoviesDatabase"
  :setvar DefaultFilePrefix "MoviesDatabase"
  :setvar DefaultDataPath "C:\Users\viiye\AppData\Local\Microsoft\VisualStudio\SSDT"
  :setvar DefaultLogPath "C:\Users\viiye\AppData\Local\Microsoft\VisualStudio\SSDT"
  
  GO
  :on error exit
  GO
  /*
  Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
  To re-enable the script after enabling SQLCMD mode, execute the following:
  SET NOEXEC OFF; 
  */
  :setvar __IsSqlCmdEnabled "True"
  GO
  IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
      BEGIN
          PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
          SET NOEXEC ON;
      END
  
  
  GO
  USE [$(DatabaseName)];
  
  
  GO
  /*
   Pre-Deployment Script Template                         
  --------------------------------------------------------------------------------------
   This file contains SQL statements that will be executed before the build script.   
   Use SQLCMD syntax to include a file in the pre-deployment script.          
   Example:      :r .\myfile.sql                              
   Use SQLCMD syntax to reference a variable in the pre-deployment script.        
   Example:      :setvar TableName MyTable                            
                 SELECT * FROM [$(TableName)]                 
  --------------------------------------------------------------------------------------
  */
  Drop table [dbo].MovieActors
  Drop table [dbo].Movies
  Drop table dbo.Actors
  Drop table dbo.Producers
  GO
  
  GO
  /*
  Post-Deployment Script Template                         
  --------------------------------------------------------------------------------------
   This file contains SQL statements that will be appended to the build script.       
   Use SQLCMD syntax to include a file in the post-deployment script.         
   Example:      :r .\myfile.sql                              
   Use SQLCMD syntax to reference a variable in the post-deployment script.       
   Example:      :setvar TableName MyTable                            
                 SELECT * FROM [$(TableName)]                 
  --------------------------------------------------------------------------------------
  */
  INSERT INTO dbo.Actors (Id,Name,Sex)
  values (1,'Actor1','MALE')
  
  INSERT INTO dbo.Producers (Id,Name,Sex)
  values (1,'Producer1','MALE')
  
  INSERT INTO dbo.Movies (Id,Name,ProducerId)
  values (1,'Movie1',1)
  
  Insert into dbo.MovieActors (Id,MovieID,ActorID)
  values (1,1,1)
  
  GO
  
  GO
  PRINT N'Update complete.';
  
  
  GO
  </code></pre>
  
  <p>As can be seen , the create tables script are not there in this script which is used for publish .Why? and is this the correct way to go about requirement where I need to create tables from scratch?</p>