Re: Stored procedure, which table is used ?

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 02/20/04 

Date: Fri, 20 Feb 2004 09:50:53 +0100

I believe that you can mark a stored procedure as a "proper" system stored
procedure (meaning some bits are set in sysobjects.status for the proc) and
doing so it will reference local table even for non system tables. I haven't
really played with this as it isn't supported. There's a system proc called
something like sp_MS_mark.... or something along those lines. Use at own
risk etc etc. 

-- 
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"ben brugman" <ben@niethier.nl> wrote in message
news:%23ixkxNv9DHA.2736@TK2MSFTNGP10.phx.gbl...
> A stored procedure in the master database.
>
> Which table does a stored procedure use when
> run in the Query analyser under local database.
> If I do a 'normal' select from, it tries to read the
> table form master.
> During an exec the table comes from local database.
>
> Why the difference ?
>
> What should I do to 'force' the 'local' table from  database ?
>
> Using an exec around all my queries is less elegant.
> (more confusing and error prone).
> Is there a design that I am missing ?
>
> ben brugman
>
>
> RESULTS FROM HERE :
>
> OKKI
> ------
> Master
>
> (1 row(s) affected)
>
> OKKI
> ----------
> LocalDatabase
>
> THE DEMO CODE :
>
> -- In the master database.
> use master
> go
> CREATE PROCEDURE sp_demo  AS
> select * from demotable
> exec ('select * from demotable')
> GO
>
>
> -- In the master database.
> use master
> select 'Master' as OKKI INTO DEMOTABLE
>
>
> -- In another database.
> use LocalDatabase
> select 'LocalDatabase' as OKKI INTO DEMOTABLE
>
> --In the query analyser run the sp_demo under
> --the another database.
> --
> --
> print 'RESULTS FROM HERE :'
> use LocalDatabase
>
> exec sp_demo
>
> use LocalDatabase
> drop table demotable
>
> use master
> drop table demotable
> drop procedure sp_demo
>
>

Relevant Pages

  • Stored procedure, which table is used ?
    ... A stored procedure in the master database. ... select 'LocalDatabase' as OKKI INTO DEMOTABLE ...
    (microsoft.public.sqlserver.server)
  • Re: Extended Stored Procedure: Get the current db of the client
    ... the database context as a parameter if you need it, ... Did you ever write an extended stored procedure? ... Master, that's what. ... database you own in order to get the current database context out of it. ...
    (microsoft.public.sqlserver.odbc)
  • Re: Extended Stored Procedure: Get the current db of the client
    ... pick up the database context, but that does not work for an Extended Stored ... you would have to create a Stored Procedure ... Master, that's what. ...
    (microsoft.public.sqlserver.odbc)
  • Re: Excute a Stored Procedure from any Database
    ... You can make your own "system" stored procs and store them in master. ... I have a Stored Procedure which I have stored in my master database ...
    (microsoft.public.sqlserver.server)
  • Stored procedure, which table is used ?
    ... >run in the Query analyser under local database. ... probably be run in Master even if called from a user db. ... >A stored procedure in the master database. ... >select 'LocalDatabase' as OKKI INTO DEMOTABLE ...
    (microsoft.public.sqlserver.server)