Having recently read Boris Baliner’s article on “Quickly Viewing Available
Space” (http://www.sqlservercentral.com/columnists/bBaliner/quicklyviewingavailablespace.asp)
and reading the ensuing conversation, it struck me that I knew a way to use
Boris’ space formula and to execute it for every database on a server at the
same time. This is what many of the users in the forum were asking for.
The trick is in knowing about and how to use the undocumented stored procedure
XP_EXECRESULTSET. This stored procedure takes two arguments, both NVARCHAR. The
first is a query to run or the “result set” and the second is the database to
run the “result set” on. Being an undocumented stored procedure, Microsoft
seems to have removed this from SQL 2005. However, SQL 2005 in SSMS has the
functionality of the Summary tab which provides a colorful and more informative
view than what is available for SQL 2000 under SSMS. Additionally, the solution
below works in SQL Query Analyzer as well.
The hardest part in using XP_EXECRESULTSET is in understanding how the “result
set” should be expressed. Let’s look at a simple example:
EXECMASTER..XP_EXECRESULTSET N'Select ''Select * from sysfiles''', N'Northwind'
Notice that the “result set” is actually a SELECT statement in and of itself
that reads:
Select ‘Select * from sysfiles’
This is the crux of using XP_EXECRESULTSET; the result set needs to be a
statement that returns a result that contains a statement that returns a
result. You cannot, for example, do the following:
EXECMASTER..XP_EXECRESULTSET N'Select * from sysfiles',N'Northwind'
This is also the power of XP_EXECRESULTSET, but more on that later.
For the current problem, how to get database file size information from all the
databases at the same time, I wrote the following script:
If you uncomment the PRINT command you will see the string that winds up in the
result set is:
SELECT 'SELECT name AS [File], filename as [File Name]
, CAST(size/128.0 as DECIMAL(10,2)) AS [Size in MB]
, CAST(FILEPROPERTY(name, ''SpaceUsed'')/128.0 as DECIMAL(10,2)) as [Space Used]
, CAST(size/128.0-(FILEPROPERTY(name, ''SpaceUsed'')/128.0) AS DECIMAL(10,2)) AS [Available Space] FROM SYSFILES'
The WHILE loop iterates through each of the user databases (which is what the
sid >1 is for) and runs the result set against each database using
XP_EXECRESULTSET.
The output:
As I mentioned above, this isn’t all you can do with XP_EXECRESULTSET. You can
actually use the result set to replace values within the query you want to run
on each database. For example, say you want to grant the ‘execute’ right to a
given user in a given database to all the user stored procedures in that
database. First, you need a query that will grant that right:
GRANT EXECUTE ON USP_PROC1 TO BOB
And a query to find all of the user stored procedures (this only works if you
use a naming convention like usp_ to prefix user stored procedures which is a
recommended practice)
SELECTNAME FROM MyDB..SYSOBJECTS
WHERE TYPE='P' AND NAME LIKE 'USP%'
Now, here comes the easy part: combine the two.
Just kidding! That’s the hard part, isn’t it? The trick is to remember
everything is one big SELECT statement. You can tinker with the statement until
you get the result you want before passing it to XP_EXECRESULTSET. So,
something like this:
DECLARE @SQL NVARCHAR(4000)
SET @SQL='SELECT ''GRANT EXECUTE ON ''+NAME+'' TO Bob ''FROM MyDB..SYSOBJECTS WHERE TYPE= ''P'' AND NAME LIKE ''USP%'''
PRINT@SQL
Would create a @SQL string of:
SELECT 'GRANT EXECUTE ON '+NAME+' TO Bob 'FROM MyDB..SYSOBJECTS WHERE TYPE='P' AND NAME LIKE 'USP%'
Something to notice here: See how the +NAME+ is actually part of the SELECT
statement and not part of the GRANT statement? The GRANT statement is nested
inside the SELECT statement. This allows the name of each stored procedure that
meets the WHERE clause to be substituted in the GRANT statement. Effectively,
this makes the statement appear to SQL as:
GRANT EXECUTE ON usp_spproc1 TO BOB
GRANT EXECUTE ON usp_spproc2 TO BOB
GRANT EXECUTE ON usp_spproc3 TO BOB
GRANT EXECUTE ON usp_spproc4 TO BOB
…and so on
A more flexible version of this idea is below:
Hopefully you will find XP_EXECRESULTSET as useful as I have. Unfortunately, I’m
going to have to find new ways to do some management tasks I use this for in
SQL 2000 when I start working on SQL 2005 servers.
