In the result, which Figure 1 shows, note that SQL Server accessed T1 first in Northwind, then in Pubs.
When you use dynamic execution in your stored procedure, SQL Server resolves user objects in the invoking database the same way it resolves system objects even if you don't mark the stored procedure as a system object. To demonstrate this behavior, run the code that Listing 2 shows to create a stored procedure that accepts a table and column name and returns all values in the given column in that table. When you force the execution of the procedure in Pubs and Northwind, SQL Server resolves the object names within the specified database, and the following code runs successfully:
EXEC pubs..sp_getcolumn
@table_name = authors,
@col_name = au_id
EXEC Northwind..sp_getcolumn
@table_name = Orders,
@col_name = OrderID
Sp_ Prefix for Other Objects
The unique functionality of special procedures is common knowledge, but it applies to other object types as well. I learned from Kalen Delaney that if you create views and tables in master and prefix their names with sp_, they behave differently than the documented way that views and tables behave. A table prefixed with sp_ behaves similarly to a special procedure in that you can access the table from any database without qualifying it with master.
To demonstrate this concept, run the code that Listing 3 shows. The code creates the sp_digits table in master, then changes the database context to Pubs and issues a few INSERT statements without qualifying the table name. At callout A, the code changes the database context to Northwind, issues a few more INSERT statements, then changes the database context to tempdb and queries the table. You can modify and retrieve data from sp_digits regardless of your database context even if you don't qualify the name with master. The final query in Listing 3 should produce 10 rows, each containing a different digit in the range 0 to 9.
Views in the master database that have the sp_ prefix are also accessible from any database without the master qualifier, but they behave a bit differently from stored procedures. With special views, SQL Server always resolves references to system objects in the context of the master database no matter which database you're querying from. As an example, run the following code to create the sp_vgetusertables view:
USE master
GO
CREATE VIEW sp_vgetusertables AS
SELECT name FROM sysobjects
WHERE type = 'u'
Then, query the view from different databases:
USE master
SELECT * FROM sp_vgetusertables
USE Northwind
SELECT * FROM sp_vgetusertables
USE pubs
SELECT * FROM sp_vgetusertables
Note that you always get the table list from master.
Prev. page
1
2
[3]
4
5
next page 
