Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004

    Question 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:

    select name from sysobjects
    where type='U'
    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:

    SELECT count(*)
    FROM sysExcludeMaint
    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?

  2. #2
    Join Date
    Aug 2002
    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.

    --Satya SKJ
    Microsoft SQL Server MVP

  3. #3
    Join Date
    May 2004

    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

    however this also fails.

    Any insights appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts