In SQL Server Data Tools (SSDT) one way to create a new object is to right click on a folder in Solution Explorer and point to Add. Doing so will display this menu:
A number of options are offered for creating a brand new object (“function”, “table”, “stored proc” etc…) and one of them is “Script”. If your preference is to handcraft the DDL for every database object without resorting to a GUI then you may choose to use the “Script” option because, after all, every file is just a DDL script – why not write the thing from scratch? There is however one fundamental difference between the Script” option and all the other options that you should be aware of.
For the screenshot below I added a script and handcrafted a simple CREATE TABLE statement:
Notice that the table is defined in the dbo schema but it is not appearing in SQL Server Object Explorer under the dbo schema node. Why is that? The answer is actually pretty simple. If I select Script1.sql in Solution Explorer and hit F4 the properties window appears and we see this:
The Build Action property of the file is set to “None” which means that the file does not get included in a build and hence any objects defined within that file will not appear in our database. This is the default behaviour when you Add as script whereas the default behaviour when you explicitly add an object is to set Build Action=Build. That is the fundamental difference between adding a script and adding a specific object.
This is one of those nuances of SSDT that is an easy problem to spot when you know about it but can cause you to spend hours hunting around for a solution if you do not (believe me, I’ve been there). Be aware of it!
@Jamiet