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.