Click here to Skip to main content
Click here to Skip to main content
Go to top

SQL Database Table Space Used Data as a CSV File

, 1 Feb 2006
Rate this:
Please Sign up or sign in to vote.
This is a simple console application that can be scheduled to create CSV files with table space used data.

Introduction

Do you have or have you ever managed a legacy SQL database that has become large and unwieldy? This tool can be executed at anytime, even as a scheduled task, to create a unique date and time stamped CSV file with a report of the table space used for each table in the database, using the sp_spaceused stored procedure.

Background

This article uses my previous submission Custom Configuration SQL Connection String Section to iterate through a defined collection of SQL connection strings to generate the CSV files. The SQL itself is quite simple, and uses a cursor to iterate through all the tables defined in the sysobjects table to execute the sp_spaceused stored procedure.

Using the code

Using the application is quite simple. For each SqlConnectionString defined in the App.Config file, a SqlConnection is established and a cursor is used to iterate through the tables found in the database to generate a CSV file. The file name format is [Database Name].TableSpaceUsed.[yyyy_MM_dd_HH_mm].CSV and can be easily changed in the Main method.

This sample assumes that the (local) instance of the SQL Server has the Model, pubs, and Northwind databases and that the account the application is running under has permissions to access the tables.

The project can be easily modified to store the results in another SQL database or an XML file.

Points of Interest

I used this tool at my employer's because we have a database that has grown by 19% in five months from 8 GB to 9.6 GB, and it has been determined that over 1/2 of the capacity is consumed in three tables. We are now researching how to flatten those tables to reduce the database size.

History

  • First version, January 26th, 2006.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Ted Bouskill
Software Developer (Senior)
Canada Canada
Very experienced Senior Software Developer/Manager with natural leadership and proven project management skills.
 
Started in Civil Engineering leading the integration of PC's into the Engineering process as a Design/Drafting Technician as well as an in-house Software Developer then transitioned into a full time Software Development career in the early 90's.
 
Ability to change and adapt has led to diverse experience with a wide array of technology and roles from graphics or web development to designing line of business enterprise applications.
 
Knowledge Base: Sharepoint, C#, SQL, ASP.NET, C++, CSS, HTML, JavaScript, XML, XSLT

Comments and Discussions

 
GeneralconnectionString PinmemberMark_WebGuru4-Sep-06 5:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140921.1 | Last Updated 1 Feb 2006
Article Copyright 2006 by Ted Bouskill
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid