18

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?)

Improve this question
  • 2
    I have a friend who keeps all his scripts in one giant .sql file. That way you only ever have to keep track of the one file. – Max Vernon♦ Nov 22 '12 at 20:57
  • I know of folks that just keep them in a folder that is sync'd to their DropBox account. Then ones that save it on their SkyDrive as well. I am not really one to use the projects in SSMS. – user507 Nov 22 '12 at 21:32
  • This is where Script Manager could come handy, if you get used to it. Though I liked it, for me it kinda didn't click. I just prefer to save my scripts locally, in some folders that I back up on DropBox. Never liked SSMS projects, too. – Marian Nov 22 '12 at 22:59
  • you can use SQLTreeo, here' s the tool: sqltreeo.com – user30997 Nov 28 '13 at 15:39
9

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.

Improve this answer
  • This is handy. It's worth noting that under the hood SSMS is storing all these files in a flat file structure in a single directory, so you can't have a file with the same name in two different folders. – Jon Sep 23 '16 at 9:27
  • @Jon – This is not quite true. It may look confusing at first sight, but file and folder structure shown in project tree is independent from loaction of files on the disk. On the disk, it does not need to be flat (just try saving new files elsewhere...), you can have them distributed in directories as you wish. In other words: keep files in any directories in the file system and then you can add them into any virtual folders in SSMS project tree. (If I remember correctly, even duplication of references is possible.) Downside: you need to organize files twice – in dirs and in project folders. – miroxlav Sep 23 '16 at 19:24
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!

Improve this answer
  • Far from an answer but this is the least kludgy hack suggested so far! Thanks for sharing, I'll keep this open for a few more days ... – DeepSpace101 Nov 23 '12 at 7:15
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.

Improve this answer
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!).

Improve this answer
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.

Improve this answer
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.

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!

Improve this answer
  • This didn't work for me. It fails to open. – Soham Dasgupta Jul 4 '18 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.