Master DB stored procedures.

crashing_suxcrashing_sux Posts: 3
edited June 8, 2007 12:31PM in SQL Prompt Previous Versions
I saw another thread on cross database coding which would be a nice feature in some future release but a feature I would like to see implemented a little differently is access to master db stored procedures named sp_*.

This fits with how sql server works currently, when executing any stored procedure named sp_* it first checks the master database for the stored procedure, and if it does not exist it then checks the current db for the procedure. Currently if I do an EXEC sp_dboption sql prompt does not give me the parameter list. Having to use EXEC master..sp_dboption would be a workable solution but just using EXEC sp_dboption feels closer to how most of us work.

-Shane

Comments

  • I've tried this and it seems to work for me. I can type in EXEC sp_dboption and I'm given the parameters. This happens even when the query is under the focus of another database.

    Are you using SQL Prompt 3.1? Have you ticked Include system objects in the candidate list, in the options under the Candidates tab?
  • System stored procedures from the master database are shown, but not user stored procedures. An example is a function I have to quickly output tables as insert statements which I keep in the master database called sp_GenerateInsertSqlFromTable. Even though it follows the naming convention for sql server to look in the master database for it when I'm in a user database SqlPrompt will still miss it because it's a user stored procedure, not a system stored procedure.
  • PDinCAPDinCA Posts: 642 Silver 1
    I've used the following top & tail code to turn a SP named sp_% in master into a system stored procedure... (Borrowed from somewhere I can't remember, so can't give them credit...) Still can't see the SP in SQL Prompt 3.1 (after Cache Refresh) though it's visible in the master DB's System Stored Procedures list... Maybe there's a bug... ("Include system objects in the candidate list" is definitely ON).
    -- This turns the SS2k's system marking on
    EXEC master.dbo.sp_MS_upd_sysobj_category 1
    GO
    ...
    Your SP
    ...
    -- This turns the SS2k's system marking off
    EXEC master.dbo.sp_MS_upd_sysobj_category 2
    GO
    
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    My guess is that System-type objects are filtered out same as the SQL Compare software does, because Prompt and Compare share some of the same code.

    I think that the list of 'system objects' is hard-coded. I can look into that tomorrow and let you know.
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Prompt may still have this issue:

    http://www.red-gate.com/messageboard/vi ... php?t=4202

    Does it work in SQL 2000, but not 2005 (or vice-versa?)
  • PDinCAPDinCA Posts: 642 Silver 1
    I'm on 2000 - haven't tried it on 2K5 as the SP in question uses the INFORMATION_SCHEMA and I'm not sure what will happen... sorry :(
    Jesus Christ: Lunatic, liar or Lord?
    Decide wisely...
Sign In or Register to comment.