Grouping Related Stored Procedures

[AspDotNetDev]

I work with a database (SQL Server 2000) with thousands of stored procedures. In order to group them so that they are easier to manage, we have made use of numbered stored procedure groups (e.g., "SomeSPGroup;1", "SomeSPGroup;2", and so on). That's better than nothing, but still it's not so great. We have one SP group with hundreds of SP's in it. What I would really like is some way to group them into a namespace and nest further namespaces in that namespace. I can sort of do this now by naming them with dot notation, but then it becomes somewhat clumsy to use them. For example, you have to type out the full name and SQL Server Management Studio provides no recognition that these SP's are related, so they are all just listed next to the other SP's in the tree view, rather than giving them their own node that can be expanded. For example, I could name two SP's like so:

[OrderProcessing.CreateOrder]
[OrderProcessing.DeleteOrder]

Whenever I want to execute those, I have to use the full names:

EXEC [OrderProcessing.CreateOrder] @param1, @param2

And the list of SP's in SSMS shows them side by side with other SP's, like this:

...
MakeShipment
[OrderProcessing.CreateOrder]
[OrderProcessing.DeleteOrder]
ProcessPayment
...

What I would like to see is a hierarchy:

...
MakeShipment
-OrderProcessing <-- This node can expand and collapse.
CreateOrder
DeleteOrder
ProcessPayment
...

I suppose I could create my own tool or perhaps make a plugin that will do this for me, but there is still no support for namespaces in the actual SQL code (i.e., I have to use the whole name when executing it). What I would like to hear is any thoughts you have on this. Do you know of any database provider that currently has a concept of namespaces? Have you header of Microsoft planning support for this in a future version of SQL Server? Does it already exist in SQL Server and I've just missed it somehow? Do you have alternative methods for managing large groups of stored procedures (and, no, prefixes are not an appealing option to me)?

[Forum Guidelines]

[Not Active]

A Schema is somewhat equivalent to a namespace in this context.

---

I know the language. I've read a book. - _Madmatt

[AspDotNetDev]

Interesting. From what I can see, the only advantage a schema has over naming an SP with dots in it is that the square brackets are not required around the full name (e.g., I can say dbo.SomeSP instead of [dbo.SomeSP]). Would be nice if schemas could be nested, could be contextualized (e.g., using dbo; would remove the need to prefix with that schema in that scope), and had tooling support (e.g., if SSMS showed schemas in the tree view and allowed them to be collapsed/expanded to show the objects in them). I suppose they also have other advantages (applying permissions to the schema gives the same permissions to the objects it contains?), but none organize SP's in the manner I'm thinking of. Thanks for the info though.

[Forum Guidelines]

[Mycroft Holmes]

I can see a requirement for a custom SQL UI. We also make extensive use of stored proc and I find scrolling through 500+ procs irritating in the extreme. And if you miss name one it can be a royal PITA trying to find the bloody thing. Go annoy Red-Gate, they make some excellent tools and may well be interested in the idea.

Never underestimate the power of human stupidity RAH