You can easily obtain a listing of the User-Defined Stored Procedures that are in any of your databases. In fact, Microsoft had made this information very easily accessible. You only need to access a System Table view called "Sys.Procedures". This information may be easily accessed using a small TSQL code, such as the following...
SELECT * FROM sys.procedures
ORDER BY [name];
Your results will vary based on what Stored Procedures you or anyone else with access to your database have created. The following is a sample of the results I have obtained when running this code on my 'model' database.
The most important thing to keep in mind with this TSQL code is that each database may contain different User-Defined SPs.
Do you want to get a listing of EVERY single Stored Procedure in your database(s)? If so, then be sure to check out SQLServerCentral.com for one of my upcoming articles! I'll post the direct link to the article as soon as it becomes available!
You'll want to check this article out when it is published...you'll NEVER have to search the Internet again to find out what SPs are in SQL Server 2005!! UPDATE: You can read this article on SQLServerCentral.com (http://www.sqlservercentral.com/articles/T-SQL/63471/). Please leave feedback if you have a few moments.
Do you want all of your databases you create (from here on...not previously created databases) to use a specified stored procedure or set of stored procedures? If so, then create the desired Stored Procedure(s) in your 'model' database. Now any new databases created will get the SP(s) you created in the 'model' database because your new databases are based on the 'model' database!