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

-- 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

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

,db_size NVARCHAR(13)
,owner SYSNAME
,created NVARCHAR(11)
,status NVARCHAR(600)
,compatibility_level TINYINT)

-- Insert the results of sp_helpdb into the temp table

EXEC sp_helpdb

-- Select the full result set from the temp table

SELECT * FROM #helpdb

-- 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

-- 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

%d bloggers like this: