Home > SQL Server, T-SQL > Using Temp Tables to Query System Stored Procedures

Using Temp Tables to Query System Stored Procedures

System stored procedures can produce a lot of valuable information, but in many cases you need to retrieve a specific piece of data or aggregate results. Temp tables provide a great deal of flexibility in filtering and aggregating results from these procedures. Books Online will usually provide the data types of the result sets from these procedures to allow you to build the temp table.

In my environment, E:, F:, G: and I: are valid drive letters for data drives. I use the following query to pull back the free space information specifically for those drives.

USE master
GO

-- Create the temptable to hold the results of xp_fixeddrives

CREATE TABLE #drivespace
(driveletter CHAR(1)
,mbfree BIGINT)

-- Insert the results of xp_fixeddrives into the temp table

INSERT INTO #drivespace
EXEC xp_fixeddrives

-- Select the drive letter and free space, dividing by 1024 to get the value in GB
-- The WHERE predicate limits the number of drives returned
SELECT driveletter, (mbfree/1024) AS [GB Free] FROM #drivespace
WHERE driveletter IN ('e','f','g','i')

-- Clean up the temp table

DROP TABLE #drivespace

This query inserts the results of sp_helpdb into a temp table, aggregating total size and returning size per database.

USE master
GO

-- Create a temp table to hold the results of sp_helpdb

CREATE TABLE #helpdb
(name SYSNAME
,db_size NVARCHAR(13)
,owner SYSNAME
,dbid SMALLINT
,created NVARCHAR(11)
,status NVARCHAR(600)
,compatibility_level TINYINT)

-- Insert the results of sp_helpdb into the temp table

INSERT INTO #helpdb
EXEC sp_helpdb

-- Select the full result set from the temp table

SELECT * FROM #helpdb
ORDER BY db_size DESC

-- Select the total size of all databases on the system, converting to GB and rounding to 
-- 2 decimal places

SELECT ROUND(SUM(CONVERT(FLOAT,(REPLACE(db_size,'MB',''))))/1024,2) AS [Total Space (GB)]
FROM #helpdb

-- Select the size for each database, ordering by size descending

SELECT name, ROUND(CONVERT(FLOAT,(REPLACE(db_size,'MB','')))/1024, 2) 
FROM #helpdb
ORDER BY 2 DESC

-- Clean up the temp table

DROP TABLE #helpdb

These are two simple examples, but the options available are nearly limitless. INSERT INTO a temp table can be used with any system stored procedure. Queries can be written against the temp table to aggregate and filter data. Additionally, any of these queries can be run through the Central Management Server against multiple instances. I’ll show an example of this in my next post.

Categories: SQL Server, T-SQL Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: