Unanswered: system stored procedures/behavior not understood
I recall that stored procedures created in the master database and having a name beginning with sp_ are available from all databases. However, I've found varying results when invoking such a procedure from a different database. For example:
DECLARE tables CURSOR FOR
select name from sysobjects
order by name
for read only
populates a cursor with the list of tables in the database from which you are running the procedure. However this query in the same stored procedure:
WHERE @tablename like tablename
fails with "Invalid object name 'sysExcludeMaint'." if the table sysExcludeMaint
doesn't exist in the master database (it does exist in the database from which I've invoked the proc). I'm not clear on why the different results. Anyone know?
I don't see any reference for sysExcludeMaint table in the master database and its not a good practice to query system tables directly. Take help of INFORMATION SCHEMA views, where Books online consists the information required.
User System Stored Procedures/Behavior not understood
My post wasn't clear. sysExcludeMaint is my own table (I work for a company called systech, we preface our tables with 'sys' typically, I've never thought of the confusion this might introduce before), therefore I'm not trying to query a system table.
To recap, the procedure (which does database maintenance/aging) is installed in the master database, but is invoked from the database which you intend to age, the query to populate a cursor name Tables from sysObjects returns the tables in the database from which you invoked the procedure. However, when querying my (non-system table) the procedure only looks in the master database, not the database from which I've run the query.
I agree that it is bad form to add tables to master, this is what I'm trying to avoid, I'd prefer to have this procedure reference the table sysExcludeMaint in the local database (the database from which I am running), however, since the procedure is located in master, SQL Server seems to look in the master database for the table.
Of course using full table notation (database.owner.table) would solve the problem, however, I'd like this procedure to run against any database on the server, so I'm hesitant to hardcode the database into the table defintion (so like mydb.dbo.sysExcludeMaint). I've tried passing the database name to the query like :
declare @dbname = 'mydb'
SELECT * from @dbname+.dbo.sysExcludeMaint