Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|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. –  Shawn Melton 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

5 Answers 5

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!

share|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

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.

share|improve this answer

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.

share|improve this answer

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!).

share|improve this answer

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, they 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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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