Master DB stored procedures.

crashing_sux

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

Ben Pinchen

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?

crashing_sux

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.

PDinCA

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

Brian Donahue

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 Donahue

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?)

PDinCA

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