25

I'm using SSMS 2012 to talk to the SQL server 2012 and Azure SQL servers we use. I admit I'm not a SQL expert so I've been saving most of my SQL scripts for future reference. I've quickly run into 20 or so .SQL scripts in the SSMS project and they are all living under the same 'Queries' folder.

Is there a way I can create "sub-folders" in the project so I can organize my scripts properly? How do most other folks keep their scripts organized? I'm pretty sure if this bugs a novice like me, it must be a real issue for real admin (with potentially hundreds of scripts?)

DeepSpace101
  • 487
  • 1
  • 5
  • 7

6 Answers6

10

Finally, subfolders were made possible in SSMS 2016.

Cautious users: You can install SSMS 2016 and still keep using old one. See note [1] below.

Once there, start using solutions instead of projects.

Subfoldering works like this:

Adding solution subfolder:

  1. Right-click solution item (tree root) and from menu select Add > New Solution Folder.
  2. Step 1 now works also on any folder you added.

Adding SQL script:

  • Right-click any folder and from its Add menu, either add existing SQL file or new SQL file (in the list, find item type which has SQL extension).

Your Good old projects (Bad old projects)

  • You can still host projects in your solution if you see them beneficial for some legacy reasons, for hosting connections, etc. Just add new or import existing projects into the solution.

[1] Regarding upgrading:

  1. SQL Server 2008 - 2016 are officially supported by SSMS 2016. See more information at download page. For older versions, you can keep your favourite old SSMS, see point 2 below.

    • the only setting which might require your attention is Options > SQL Server Object Explorer > Script for server version > (set your server version)

  2. Different SSMS versions live well together, i.e. you can have SSMS 2008 and SSMS 2016 installed together without issues – tested.

miroxlav
  • 343
  • 3
  • 10
6

In SSMS, if you are not already displaying the Template Browser snap-in, go to Menu, View, Template Explorer (hot key Ctrl-Alt-T).

The templates are loaded from your local machine, from this folder

C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql

You can add subfolders as you like and even nest them (via Windows Explorer or SSMS). If I am not mistaken the folders are loaded only once per SSMS startup, so you may have to restart SSMS to pick up folder changes from the filesystem.

p/s - This method re-purposes the template browser for the purpose of organising project scripts!

孔夫子
  • 4,330
  • 3
  • 29
  • 49
4

Sadly SSMS projects don't support subfolders. If you are talking about the default folder that SQL saves stuff into, yes you can create subfolders in there. That is just a folder on your hard drive. That folder is located in "C:\Users{username}\Documents\SQL Server Management Studio\Projects" by default.

mrdenny
  • 26,988
  • 2
  • 42
  • 81
2

The lack of subfolders is certainly frustrating. I'm going to try organizing my stuff into separate projects in the same solution. Each one has a Queries folder... I'm going to name the projects the way I would name the subfolders (if I could create them!).

Aron
  • 21
  • 1
1

Adding to Answer 3, above, working around the situation by re-purposing templates at C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql.

You'll have to add permissions to the machines user accounts for Full Control. (Right click the destination folder, Properties, select the machine users, Edit, etc.)

Even then it's like Steve Balmer headed this solution off at the pass. You can save a file there but when it is opened in SSMS, it isn't opened under that .sql file's name, it's a generic name. So there's all this rigmarole just to keep your queries, functions, table scripts, sprocs, etc. all organized.

Great answer but on Windows 8.1, it got precluded.

The best answer I have is saving via SSMS to the file system which I think will respect the nesting of .sql files. I have to create a Database Project in Visual Studio 2013, in which I can set up folders as they make sense for my product flow.

But I can't open these queries via Server Explorer in Visual Studio ... however it is then possible to organize queries in one place if they're In Work, SPROC Scripts, UDF Scripts, etc.

There is no IDE for doing this sort of project work and yep, in the words of another respondent here, that's sad.

Rambling and ranting here a bit ... in other words I can get the folder structure I want with a DB Project in VS 2013 but if I want to log onto a remote server to test them, I have to access them file by file from SSMS. Pain in the back side ... I want an IDE that does this.

user1585204
  • 111
  • 1
0

I made progress by manually editing the SSMS project file to add folders underneath the solution. Open the .ssmssqlproj file in a text editor. Search for the nodes called LogicalFolder. The Miscellaneous node is a good model for what you need to do. 

  </p>

Just copy these lines and change the name to the folder you want to see. Also change the Type value to something unique. Here is what I did.

<LogicalFolder Name="Tables" Type="4" Sorted="true">
  <Items />
</LogicalFolder>
<LogicalFolder Name="Functions" Type="6" Sorted="true">
  <Items />
</LogicalFolder>
<LogicalFolder Name="User-defined Table Types" Type="7" Sorted="true">
  <Items />
</LogicalFolder>

MAKE SURE TO KEEP A COPY OF THE PROJECT FILE (.ssmssqlproj). If you mess up the editing, as I did a few times, the project will not open. This won't be so bad if you have installed the TFS add-in and are checking in your project files - you can just discard the changes. But if not, make sure you make a copy.

Then I create folders under the solution to match the logical folder names. After that I created a table definition script and a user-defined table type definition script, and saved them in their respective folders. They showed up under the "Miscellaneous" folder in the solution explorer, so I dragged them to the appropriate folders.

This is where things broke down. Because, I think, all of the stored procedures, functions, table, and type are in files that have a .sql extension. Because the are .sql files, they were placed under the Queries folder in solution explorer. However, they are still physically in the right folders on disk. So that is a step in the right direction.

I want to see if the "Type" attribute in the project file corresponds with a specific file extension, and if I can figure out what those are. If I can get the right extensions then SSMS will place the files in the right project folder.

I may look at Visual Studio to see how that works, since SSMS is based on Visual Studio, according to their splash screen.

However, I am partway there, so maybe one of you can figure out the rest!