SQL Server Performance Forum – Threads Archive

How to Use "Use <databasename>" in a Stored proc

I am trying to write a SP that will run through code and at some stage it needs to change
the Database… Like you would when you say: "Use Master" but this does not seem to
work at all? Does anyone have any suggestion?

---

You can reference database name as DBname.dbo.tablename or dbname..tablename to get the query results, try to post the code used. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Alternatively you can place your stored procs in the master database with a prefix of sp_
calling the stored proc from any database will then look in master database first, and if it exsists will run the CODE from the body of the master proc, but in the CONTEXT of the calling database

---

HI Satya, Thanks for the reply, Here is the code, I am trying to Automate the process so that
it will automaticly detect all DB on the server and then run the code and populate the
table. I am making use of 2 cursors – One for the DBNAME and the other for Table name. /*code*/
if exists (select * from dbo.sysobjects where id = object_id(N'[tblsize]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [tblsize]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[spt_space]’) and OBJECTPROPERTY(id, N’IsUserTable’) = 1)
drop table [spt_space]
GO
DECLARE
@id int,
@pages int,
@objname varchar(750),
@DBNAME as varchar(50) SET NOCOUNT ON CREATE TABLE tblSize
(
ServerName varchar(50),
DBName varchar (50),
Name varchar (100),
Rows varchar (100),
Reserved varchar (100),
Data varchar (100),
Index_Size varchar (100),
Unused varchar (100)
) CREATE TABLE spt_space
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
) —-declare fisrt Cursor to get DBNAME DECLARE DBNAME CURSOR FOR
SELECT Name
FROM master..sysdatabases
OPEN DBNAME
FETCH DBname into @DBName WHILE @@FETCH_STATUS = 0 BEGIN
USE @DBName — *** PROBLEM *** — declare main cursor to get first user table name from sysobjects
DECLARE TabNameCur CURSOR FOR
SELECT id, name
FROM dbo.sysobjects
WHERE xtype = ‘u’
ORDER BY name
OPEN TabNameCur
FETCH TabNameCur INTO @id, @objname WHILE @@FETCH_STATUS = 0
BEGIN TRUNCATE TABLE spt_space INSERT INTO spt_space (reserved)
SELECT sum(reserved)
FROM sysindexes
WHERE indid in (0, 1, 255)
AND id = @id SELECT @pages = sum(dpages)
FROM sysindexes
WHERE indid < 2
AND id = @id SELECT @pages = @pages + isnull(sum(used), 0)
FROM sysindexes
WHERE indid = 255
AND id = @id UPDATE spt_space
SET data = @pages UPDATE spt_space
SET indexp = (SELECT sum(used)
FROM sysindexes
WHERE indid in (0, 1, 255)
AND id = @id) – data UPDATE spt_space
SET unused = reserved
– (SELECT sum(used)
FROM sysindexes
WHERE indid in (0, 1, 255)
AND id = @id) UPDATE spt_space
SET rows = i.rows
FROM sysindexes i
WHERE i.indid < 2
AND i.id = @id
–This step required as ‘convert…/1000’ cannot be used with varchars
INSERT into tblSize
SELECT @@servername ,
@DBname ,
name = object_name(@id),
rows, –= convert(char(11), rows),
reserved = convert(decimal (8,2), (reserved * d.low / 1024.)/1000),
data = convert(decimal (8,2), (data * d.low / 1024.)/1000),
index_size = convert(decimal (8,2), (indexp * d.low / 1024.)/1000),
unused = convert(decimal (8,2), (unused * d.low / 1024.)/1000)
FROM spt_space, master.dbo.spt_values d
WHERE d.number = 1
AND d.type = ‘E’
FETCH NEXT FROM TabNameCur INTO @id, @objname
END
— close & deallocate main cursor
CLOSE TabNameCur
DEALLOCATE TabNameCur FETCH NEXT FROM DBNAME into @DBName END
DEALLOCATE DBNAME
/*Thanks */

---

From BOL: "Changes in database context last only until the end of the EXECUTE statement". Even though you can use EXEC (‘USE’ + @dbname) but the change will last only till that statement. Even if your next statement refers to some table in the changed db, it will fail because from the next statement, the db has changed to the original db again in which the procedure is being executed.

---

Thanks, IS there a work around?

---

You could use sp_msforeachdb For example: sp_msforeachdb ‘?.dbo.sp_spaceused’ sp_msforeachdb ‘USE ?; select db_name()’ sp_msforeachdb ‘USE ?; Exec SomeDB.dbo.your_proc’ ? will be replaced with each DB name and the statement will be executed for every DB.

---

Just be aware that those sp’s vaxman mentioned are all undocumented and therefore should be carefully used in production code.
–Frank
http://www.insidesql.de

---

Thanks for the Help.
Regards

---

]]>