Find stored procedure in your database with T-SQL in SQL Server 2005/2008

I have observed quite a few times in different forum, people keep asking question that I have created stored procedure in my database but I can’t find it. Well there are quite a few reason for that, either you have created your stored procedure in different database and you are trying to run in different database or you have not specified schema name for your stored procedure so default schema of your login will be automatically applied to it, while running SP, you don’t specify that schema name and you will face an error like:

“Could not find stored procedure ‘Your SP name’”

Well, to ensure that you stored procedure is created and it is exists in particular database, I used to run following very simple query to confirm.

select ss.name + '.' + sp.name as 'Available Stored Procedure' from sys.procedures as sp join sys.schemas ss on sp.schema_id=ss.schema_id

Above query will show you all the available stored procedure along with its schema name available in your database.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com