Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Using XP_EXECRESULTSET To Obtain Database Size Information and More

By J. T. Shyman,

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.

Total article views: 12668 | Views in the last 30 days: 5
 
Related Articles
FORUM

SQL select statement results to XL file

SQL select statement results to XL file

FORUM

Select statement of view stays 5 hours to return results......

Select statement of view stays 5 hours to return results......

FORUM

select statements

select statements

FORUM

select statement

select statement

FORUM

select statement

select statement

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones