syntax for system sproc from master db

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

General SQL Server Forums

New to SQL Server Programming

syntax for system sproc from master db

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-21 : 14:27:17

We have the following sproc that is in our Master db:
USE [SIZING_DB]
GO
/****** Object: StoredProcedure [dbo].[GETSTATS] Script Date: 11/21/2011 13:07:07 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GETSTATS]
WITH
EXECUTE AS CALLER
AS
CREATE TABLE #t
(
[name] NVARCHAR(128),
[rows] CHAR(11),
reserved VARCHAR(18),
data VARCHAR(18),
index_size VARCHAR(18),
unused VARCHAR(18)
)

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?'''

SELECT DB_NAME() as DBName, GETDATE() as DateCreated, *
into SIZING_DB.dbo.DBTableStats
FROM #t

-- # of rows.
SELECT DB_NAME() as DBName, GETDATE() as DateCreated, SUM(CAST([rows] AS int)) AS [rows]
INTO SIZING_DB.dbo.RowCounts
FROM #t

DROP TABLE #t

INSERT INTO sizing_db.dbo.dbstats
SELECT GETDATE() AS date_and_time,
DB_NAME() AS database_name,
SUM(total_pages) AS reservedKB,
SUM(used_pages) AS usedKB,
SUM(total_pages -used_pages) AS unallocatedKB
FROM sys.allocation_units
INSERT INTO sizing_db.dbo.dbstats2
SELECT GETDATE() AS date_and_time,
DF.name,
SIZE,
FILEPROPERTY(DF.name, 'spaceused') AS pages_used,
FILEPROPERTY(DF.name, 'spaceused') * 1.0 / SIZE * 100 AS pct_used
FROM sys.database_files AS DF

SELECT dbstats.uniquekey,
dbstats.date_and_time,
dbstats.database_name,
dbstats.reservedKB,
dbstats.usedKB,
dbstats.unallocatedKB,
dbstats2.size,
dbstats2.pages_used,
dbstats2.pct_used
FROM sizing_db.dbo.dbstats dbstats
INNER JOIN
sizing_db.dbo.dbstats2 dbstats2
ON (dbstats.uniquekey = dbstats2.uniquekey)

SELECT * FROM SIZING_DB.dbo.DBTableStats

DROP TABLE sizing_db.dbo.DBTableStats

We are 'supposed' to be able to execute this sproc from that location have it provide the data against the other db's on the server. What is the syntax for firing this off correctly? I know it is 'EXEC sp_getstats', followed by something related to one of the other dbs (i.e., Arrest, MNI, EVIDENCE, CIVIL, etc.)

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-11-21 : 14:35:26

You can't just pass the database name here. You will need to do a pretty significant code change where you'll make use of sp_executesql and use dynamic SQL instead. Take a look at the code in my isp_ALTER_INDEX stored procedure for examples of how to run code in a specific database: http://weblogs.sqlteam.com/tarad/archive/2009/11/03/DefragmentingRebuilding-Indexes-in-SQL-Server-2005-and-2008Again.aspx

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2011-11-21 : 15:08:56

I'm still looking at your example and will admit it's going to take me a while to digest it. In the meantime, i've noticed that if i run this sproc from within the master database, using, for example:
EXEC sp_GETSTATS USE Evidence
It will provide me with the result sets i'm looking for, but each time i execute it with a different USE statement, the result set is one exec cycle behind. For instance, if i were to follow the above example with EXEC sp_GETSTATS use CIVIL, it will provide me with the stats for the EVIDENCE db. If i execute the statement with the USE CIVIL suffix a second time, it provides me with the stats for CIVIL. Any ideas why?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-11-21 : 16:22:10

Because that's actually 2 statements:

EXEC sp_GETSTATS;
use CIVIL;

The exec runs and then the USE changes the database context. Hence, next time you run, the database context is Civil.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources