I know, there are standard reports and other GUI tools for doing this task for SQL Server 2005 or later but what about when you need to extract such information from a SQL Server 2000 instance or when you just want to write some code? :)
The undocumented stored procedure sp_msforeachtable
is here to help you (along with the stored procedure sp_spaceused
--Select the database to be scanned for table disk usage
--Create temporary table 1 - Sizes will be in strings/KB
create table #tmpSizes(
--Create temporary table 1 - Sizes will be in KB
create table #tmpSizesFinalKB(
--Get the disk usage per table and store in temp table 1
INSERT INTO #tmpSizes
EXEC sp_MSforeachtable @command1="sp_spaceused '?'"
--Indirect casting and copying of the information in temp table 2
--This is only when you want to store the disk usage statistics
--in a form that allows sorting operations etc.
insert into #tmpSizesFinalKB
--Access the disk usage results (in KB)
order by reserved desc
Now you can easily manipulate the disk usage statistics in temp table #tmpSizesFinalKB in order to represent them in the form you may like!
Useful? Please comment/like/tweet!
My Latest Projects:
Labels: SQL Server, SQL Server Administration, T-SQL Tips, Undocumented