Where Do SSDT SQL Scripts Connect To?

In some software projects I used empty Visual Studio solutions to create some kind of database projects. I added SQL script files to the solutions to create all database objects, like tables, functions, stored procedures, views, constraints, and so forth. A batch file was used to run all these scripts against the database.

One common part of these script files was a snippet of sample code to test the database object, e.g. a stored procedure or function, directly from inside Visual Studio. This snippet both documented the use and gave a quick way to test changes. Of course, this snippet was intended to be used only when connected to a local SQL instance to avoid damage in any other environment!

Playing around with SQL Server Data Tools (SSDT) in VS 2012, I was looking for the ability to connect a function script to my existing local database. Under the menu item SQL / Transact SQL Edit / Connection there is the ability to connect to a database. To my surprise, I was not asked to which SQL instance or database I would like to connect to. At least, clicking the connect menu item seemed to do nothing at all.

But running the script worked, and the result window told me that the function was created successfully. I was wondering where. Searching for a database selection option in the toolbars, as I know it from the SQL script files in VS 2010, failed. There is no such possibility. Was the function created in the master database? But which SQL Server instance was used?

Well, Visual Studio showed me the answer to these questions all the time, but I did not noticed it.

When you edit a function or a stored procedure script file of a SQL Server Database project – not a simple SQL Query file! – you will find these information in the properties window on the right (or wherever you have placed it). The properties window shows the connection name, logon name and so forth, and whether the connection is open or closed. You cannot change any entry. These properties are not available for table script files of the project, which can’t be connected to any database at all from within Visual Studio.

But the properties window does not tell you the name of the database. Almost, it is the project’s name. But it doesn’t have to. The name can be found in the .sqlproj.user file, located in the project directory. Of course, you can edit this file to change the name.