Is it possible to make SP/Function as private ones?

Question

I am using Microsoft SQL 2005 server. I have created several SP and Functions in TSQL. After more than one year's development, the number of SP/Functions have grown dramatically. Only a few of SP/Functions are used as scheduled jobs or triggers, and most of them are used internally(I mean they are called by SP/Functions in jobs and triggers).

With the number of SP/Functions growing, it is very hard to manage them in our SQL server. It is hard for other developer/DBAs to know their dependency and usages. I would like to keep all the dependent SP/Functions as private ones just like ones in PL/SQL's packages. I could rewrite them in CLR but customized CLR is not allowed in our SQL server.

Any way to limit SP/Function's usage scope, i.e., limit them as private in a "package"? Not sure if SQL 2008 have any features to resolve this issue?

Answer 1

In SQL Server 2005 (onwards), you can use Schemas for this purpose, BUT it does have security chaining implications.

Unfortunately, SQL Server does not yet have a 'package' equivalent, though I believe that this is something which has been requested often.

Answer 2

Isn't it ironic? The very tool that was supposed to help you reduce complexity creates more. I used SP last about 2 years ago when I created the first version of a blogging website that I was making. They caused me a lot of heartache cause I couldn't fit them well in the scheme of things (my C# code).

So finally I got rid of them and took all my SP code back to C#. That made things easier to organizer.

Now I may use Views, but I don't use SPs any more.