
SYSFILES in store proc always refer to master db only
Dear all,
I'm trying to develope some simple utility store proc and would like it able
to be used in any db. So I created in master db. I found that when I refer
to SYSFILES in store proc, it only refers to that of master db only.
However, for other system tables (e.g. SYSUSERS), that is not the case.
Neither is it happen if I send the SQL statement directly.
I've tested it on SQL 7.0 SP2, and SQL 2000 RTM. I can't find clues on BOL
nor KB. Can anyone tell me whether it is a SQL server bug, or, any other
reason?
Thanks in advance for anyone's help ... Eric
========================================================
To try, just execute the following script in master db to create the store
proc.
USE master
GO
CREATE PROCEDURE dbo.sp_t AS
SELECT * FROM SYSFILES
SELECT * FROM SYSUSERS
GO
Then try to issue the script statement directly vs in store proc
USE pubs
GO
SELECT * FROM SYSFILES
SELECT * FROM SYSUSERS
GO
EXEC sp_t
GO
Result should show that