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
Advertisements

Find fully qualified path in SQL Server 2008/2005

It has been observed so many times that when programmer creates table or stored procedure in SQL Server and when tries to run it with its name, they face an error that object doesn’t exists. If you are sure that you have created object even though it is not available even after you are having full permission in database than there is a case that, object owns by particular schema and you are not referring it while calling.
There are few schema in Adventureworks database and each owns few tables. If you try to use table name only in SELECT statement, you will face an error given below.
–try running below query
use AdventureWorks
go
select * from Address
–it will show you below given error
–Msg 208, Level 16, State 1, Line 1
–Invalid object name ‘Address’.
–reason is Address table is owned by Person schema
–so if you try like below, you are done.
select * from Person.Address
–Person.Address
–we have specified schema name and table name in above query
–but the good practise is to specify ServerName.DatabaseName.Schemaname.ObjectName
–this is called fully qualified path and to find out fully qualified path of object
–use below give T-SQL statement.
select ‘[‘+@@SERVERNAME+‘]’  + ‘.’ +DB_NAME() + ‘.’ +ss.name+‘.’+so.name as ‘Full Qualified Name’ from sys.objects so join sys.schemas ss
on so.schema_id=ss.schema_id where so.name=‘Address’



Enjoy!!!!
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