Quickly Retrieve a List of Available Media Resources

The sp_GetAllSQLMediaTypes stored procedure lists the names of all the available media (i.e., drives) and their types (e.g., hard disk, tape, floppy, CD-ROM) on a SQL Server machine. As Listing 1 shows, sp_GetAllSQLMediaTypes uses a relatively unknown extended procedure called xp_availableMedia. After GetAllSQLMediaTypes runs xp_availableMedia, sp_GetAllSQLMediaTypes fills a temporary table with the results. It then decodes the media type values into meaningful terms based on the information under Media Type Constants (SQLDMO_MEDIA_TYPE) in SQL Server Books Online (BOL).

To use sp_GetAllSQLMediaTypes, you run the code

USE master GOEXEC GetAllSQLMediaTypes 

I wrote and tested sp_GetAllSQLMediaTypes on a Windows XP machine running SQL Server 2000 Service Pack 1 (SP1). Figure 1 shows the results when I ran it on my machine.

Editor's Note
Share your SQL Server discoveries, comments, problems, solutions, and experiences with products and reach out to other SQL Server Magazine readers. Email your contributions (400 words or less) to [email protected]. Please include your phone number. We edit submissions for style, grammar, and length. If we print your submission, you'll get $50.