SQL Server Stored Procedure Folders/Grouping

Question

We are currently using SQL Server 2000 but will soon be moving to 2008. I am looking for a way to group related stored procedures into folders. SQL Server 2000 does not seem to have this ability and from my searches it looks like 2008 does not either. This seems like a basic feature that most users would want. Wouldn't it make sense to put all generic stored procedures that are shared across multiple projects in one folder and project specific procs in another?

It seems the way most devs do this now is by using some from of ID_SPNAME syntax and sorting them.

Answer 1

Grouping procs and functions by type in the UI would be nice, but Management Studio can't do it. Using SQL Server 2000, I've done what you suggest (prefixing objects with a grouping code and sorting). In 2005 and 2008, consider creating schemas to serve the same purpose, and grouping your objects in those. Note that the object names are prefixed with the schema name in the UI.

CREATE SCHEMA ShoppingCart AUTHORIZATION Joe
    CREATE PROCEDURE AddItem ...

... will display in the UI as ShoppingCart.AddItem.

Schemas in Sql Server 2008

Answer 2

The most common way to do this (in SQL 2005/2008) is by using schemas:

HR.spCalculateEmployeeCompensation
HR.spCalculateContractorBonus
Web.spAppendWebLog
Web.spUserEndSession
Reporting.spGetCurrentYearSummary
Reporting.spGetLastMonthDetail

Not only will these visually organize themselves in the SSMS list, but you can apply different permissions to each schema.