INFORMATION_SCHEMA again

Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
use tempdb
go
if object_id('temp_proc') is not null drop proc temp_proc
go
create proc temp_proc
as
SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
go
exec tempdb.dbo.temp_proc
use master
exec tempdb.dbo.temp_procThe procedure is still executed in the context of tempdb, not of master. I
think it would be very unexpected behavior if you changed information_schema
views to real user tables, and calling pubs.dbo.someprocedure from tempdb,
tried to find an authors table in tempdb?
A
"Jay" <nospan@.nospam.org> wrote in message
news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
> use tempdb
> go
> if object_id('temp_proc') is not null drop proc temp_proc
> go
> create proc temp_proc
> as
> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
> FROM INFORMATION_SCHEMA.TABLES
> go
> exec tempdb.dbo.temp_proc
> use master
> exec tempdb.dbo.temp_proc
>|||Huh?
Are you saying that a procedure will always execute within the database it
was created in?
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:utSMLyqCIHA.1204@.TK2MSFTNGP03.phx.gbl...
> The procedure is still executed in the context of tempdb, not of master.
> I think it would be very unexpected behavior if you changed
> information_schema views to real user tables, and calling
> pubs.dbo.someprocedure from tempdb, tried to find an authors table in
> tempdb?
> A
>
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
>> use tempdb
>> go
>> if object_id('temp_proc') is not null drop proc temp_proc
>> go
>> create proc temp_proc
>> as
>> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
>> FROM INFORMATION_SCHEMA.TABLES
>> go
>> exec tempdb.dbo.temp_proc
>> use master
>> exec tempdb.dbo.temp_proc
>>
>|||Would something else make more sense?
USE Pubs;
GO
CREATE PROCEDURE dbo.GetAuthors
AS
SELECT * FROM Authors;
GO
USE master;
GO
EXEC Pubs.dbo.GetAuthors;
You expect this to return rows from master.dbo.Authors?
"Jay" <nospan@.nospam.org> wrote in message
news:uqcmv$qCIHA.4228@.TK2MSFTNGP02.phx.gbl...
> Huh?
> Are you saying that a procedure will always execute within the database it
> was created in?
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:utSMLyqCIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> The procedure is still executed in the context of tempdb, not of master.
>> I think it would be very unexpected behavior if you changed
>> information_schema views to real user tables, and calling
>> pubs.dbo.someprocedure from tempdb, tried to find an authors table in
>> tempdb?
>> A
>>
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
>> use tempdb
>> go
>> if object_id('temp_proc') is not null drop proc temp_proc
>> go
>> create proc temp_proc
>> as
>> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
>> FROM INFORMATION_SCHEMA.TABLES
>> go
>> exec tempdb.dbo.temp_proc
>> use master
>> exec tempdb.dbo.temp_proc
>>
>>
>|||I expected to be able to write a general purpose procedure that would detect
the catalog of the database it was executed in. In the example you gave, I
would expect it to complain that there was no Authors table, not go back to
the database the procedure was created in and read the Authors table in
there while the specified database context was master.
However, looking back at the nightly maintenance procedure I wrote, do do
this I have to get cute with dynamic sql and embeded 'use' statemants.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e%23NBYCrCIHA.4472@.TK2MSFTNGP05.phx.gbl...
> Would something else make more sense?
>
> USE Pubs;
> GO
> CREATE PROCEDURE dbo.GetAuthors
> AS
> SELECT * FROM Authors;
> GO
> USE master;
> GO
> EXEC Pubs.dbo.GetAuthors;
>
> You expect this to return rows from master.dbo.Authors?
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:uqcmv$qCIHA.4228@.TK2MSFTNGP02.phx.gbl...
>> Huh?
>> Are you saying that a procedure will always execute within the database
>> it was created in?
>>
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:utSMLyqCIHA.1204@.TK2MSFTNGP03.phx.gbl...
>> The procedure is still executed in the context of tempdb, not of master.
>> I think it would be very unexpected behavior if you changed
>> information_schema views to real user tables, and calling
>> pubs.dbo.someprocedure from tempdb, tried to find an authors table in
>> tempdb?
>> A
>>
>>
>> "Jay" <nospan@.nospam.org> wrote in message
>> news:uihhguqCIHA.1168@.TK2MSFTNGP02.phx.gbl...
>> Why isn't INFORMATION_SCHEMA.TABLES seeing the master database tables?
>> use tempdb
>> go
>> if object_id('temp_proc') is not null drop proc temp_proc
>> go
>> create proc temp_proc
>> as
>> SELECT TABLE_CATALOG , TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
>> FROM INFORMATION_SCHEMA.TABLES
>> go
>> exec tempdb.dbo.temp_proc
>> use master
>> exec tempdb.dbo.temp_proc
>>
>>
>>
>