One Way to Build an SSMS Database Solution – Add a Table and a View

This is the third post in a series named One Way to Build an SSMS Database Solution. Please find additional posts:

  1. One Way to Build an SSMS Database Solution – Introduction
  2. One Way to Build an SSMS Database Solution – Add a New Query
  3. One Way to Build an SSMS Database Solution – Add a Table and a View
  4. One Way to Build an SSMS Database Solution – Add a Stored Procedure and the Build

Create a Table Named T in the S Schema

Click the “New Query” button in SSMS and add the following T-SQL:

Once added, the T-SQL should appear similar to:

If all goes according to plan, the first execution should generate the following messages:

Subsequent executions should generate the following messages:

Use File>Save As to save the T-SQL script as “2-Create_s_t.table.sql” to the SSMS project directory.:

Once saved, the file should appear in the SSMS Solution Explorer:

Next, move the T-SQL script file named “2-Create_s_t.table.sql” into the SSMS project directory by clicking and dragging the file over the Queries virtual folder:

Release the mouse to drop the file into the Queries virtual folder:

The method described for adding the “2-Create_s_t.table.sql” T-SQL script to the SSMS project is my preferred method for so doing.

Create a View Named V in the S Schema

Click the “New Query” button in SSMS and add the following T-SQL:

Once added, the T-SQL should appear similar to:

If this script to create the s.v view is executed as is, a DDL error results:

“Msg 111, Level 15, State 1, Line 18
‘CREATE VIEW’ must be the first statement in a query batch.”

Note that the last print statement executes and displays ” – s.v view created”. this print statement is inaccurate, as seen when examining SSMS Object Explorer (post-refresh):

One way to correct the error is by adding the default SSMS batch separator – “go” – just before the Create View T-SQL DDL statement:

Once added, the T-SQL should appear similar to:

The s.v DDL T-SQL statement should now execute successfully.

If all goes according to plan, the first execution should generate the following messages:

Subsequent executions should generate the following messages:

In SSMS Solution Explorer, save the T-SQL script as “3-Create_s_v.view.sql”:

Next, drag the “3-Create_s_v.view.sql” T-SQL script into the Queries virtual folder in Solution Explorer:

Conclusion

In this post we examined how to create scripts that add a table and a view. Next up: Adding a stored procedure and the Build script.

Andy Leonard

andyleonard.blog

Christian, husband, dad, grandpa, Data Philosopher, Data Engineer, Azure Data Factory, SSIS, and Biml guy. I was cloud before cloud was cool. :{>

Comments

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.