executing sp with database context

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

executing sp with database context

Author

Topic

hughdutoit
Starting Member

2 Posts

Posted - 2006-12-11 : 05:49:14

hi,

i want to create a stored procedure in SQL 2005 that can be executed from any database, using that database's context. it must seem as if the sp resides in that database.

i created the sp in the master database, and it sort of works, but not 100%. if the sp (lets call it sp_context) just has "select db_name()" in it, and you call it via other databases like "exec mydb.dbo.sp_context", or "exec testingdb.dbo.sp_context", then the sp acts like expected and returns the calling DB's name.

so far so good

but my actual sp, is one i pull directly from BOL (search for sys.dm_db_index_physical_stats dynamic management function), that will re-index and re-organize my indexes. it must be executed with the calling DB's context so that DB's indexes are checked, but it does work.

SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname sysname;
DECLARE @objectname sysname;
DECLARE @indexname sysname;
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command varchar(8000);
-- ensure the temporary table does not exist
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
-- conditionally select from the function, converting object and index IDs to names.
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;

-- Open the cursor.
OPEN partitions;

-- Loop through the partitions.
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;

WHILE @@FETCH_STATUS = 0
BEGIN;
SELECT @objectname = o.name, @schemaname = s.name
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;

SELECT @indexname = name
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
IF @frag < 30.0
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @objectname + ' REORGANIZE';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;

IF @frag >= 30.0
BEGIN;
SELECT @command = 'ALTER INDEX ' + @indexname +' ON ' + @schemaname + '.' + @objectname + ' REBUILD';
IF @partitioncount > 1
SELECT @command = @command + ' PARTITION=' + CONVERT (CHAR, @partitionnum);
EXEC (@command);
END;
PRINT 'Executed ' + @command;

FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;

-- drop the temporary table
IF EXISTS (SELECT name FROM sys.objects WHERE name = 'work_to_do')
DROP TABLE work_to_do;
GO

if you put the above into a stored procedure in master and you call it form another DB it seems that sometimes its acting asif in the context of the calling DB, but other commands its executing as in the context of 'master' :(....

for example, "FROM sys.dm_db_index_physical_stats" will pull data from the calling DB, but "FROM sys.objects " is pulled from master??

any advise?

thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-11 : 07:40:59

you can't put USE in the sproc.
you can put the db name as a parameter and use dynamic sql.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-11 : 08:50:34

From what I understand, If you create the stored procedure in master and then execute it specifying the DB and owner it sort of does a USE DB first

so Execute mydb.dbo.sp_myStoredProc

will execute sp_myStoredProc from master, but in the context of mydb. I've not tested this in SQL2K5, but it does work in sql2K. In my case the Stored procedure returns the space occupied by the n largest tables in the db, and does reference sysobjects.

--
Regards
Tony The DBA

hughdutoit
Starting Member

2 Posts

Posted - 2006-12-11 : 09:28:35

yes, thats exactly what i'm trying to do.. but try in SQL2005 and create this simple sp in master:

create PROCEDURE [dbo].sp_DbContextTest
AS
SELECT *
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
where type_desc = 'USER_TABLE'

does not matter from what database you call it then, it returns master's results, instead of the calling DB's

TonyTheDBA
Posting Yak Master

121 Posts

Posted - 2006-12-11 : 09:55:45

Well, Thats a b*gg*r

And even more bizzarely

DECLARE @sql Varchar(256)
      , @DBName Varchar (128)

SELECT @DBName = 'AdventureWorks'

SELECT @Sql = 'USE ' + @dbName +' ; EXECUTE sp_DbContextTest'

EXECUTE ( @Sql )

produces exactly the same results

--
Regards
Tony The DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources