10

When I was exploring the master database in SSMS I noticed that under the Tables folder there is another folder called Systems Tables that houses a bunch of tables. Is it possible for us to create a structure akin to Systems Tables within our database?

I am looking to organize tables and stored procedures into project specific folders. Under the new setup - when I am referring to my table object I would have to use the following syntax (I am guessing here):

[dbName].[projectName].[dbo].[tableName] 

Also, apart from clearing up the clutter, do anybody foresee any performance improvement/degradation because of this re-organization? I use Microsoft SQL Server 2008 R2.

2 Answers 2

10

There is no way to create custom folders directly in SQL Server Management Studio.

Here are a few common strategies:

  1. Group database objects by schema to help one-dimensional organization
  2. Name objects from broad to specific areas

For the 1st option:

billing.Product
billing.Receipt
internal.Employee
internal.Office

Schemas are most primarily used to logical grouping of database objects, and can also provide an extra layer of security when using more than just the out-of-the-box configuration. And the secondary benefit is, of course, the grouping of these items within SSMS Object Explorer.

For the 2nd route (name objects from broad to specific), it'd be something like this:

dbo.Animal
dbo.AnimalDiet
dbo.AnimalDietFootType
dbo.AnimalHabitat
dbo.Person
dbo.PersonAddress
dbo.PersonAutomobile
dbo.PersonAutomobileArchive

So on an so forth. You can see that these objects alphabetically order themselves. It's not just good for visual organization, but a great naming convention in my opinion.

2
  • Just gonna add here that schemas are also useful to control access to logical groups of objects in the database. That is, if you put your reports in their own schema, then those reports can run as a user that only has access to the reports schema. ... Though an even better way is to give the reports user only access to execute the specific report stored procs it needs. Just a bit easier to manage on a per-schema basis.
    – tsilb
    Jun 10, 2020 at 22:09
  • FYI for the first option, see is it okay to put dots in SQL Server database names?. Option 2 may be a better approach for most.
    – ctwheels
    Jul 17, 2020 at 14:03
8

This functionality isn't provided by Management Studio.

However, there are 3rd party add-ins that offer this feature, e.g. SQLtreeo and SQL Handy Groupie. I'm not affiliated with either of these companies, nor can I vouch for the reliability / stability of their add-ins or whether or not they will meet all of your requirements.

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