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