How to rebuild all the indexes of a database in SQL Server

Getting the Disk Usage Statistics for all Tables in a Database

by

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

 

Get the disk usage statistics using the undocumented stored procedure sp_msforeachtable

The undocumented stored procedure sp_msforeachtable is here to help you (along with the stored procedure sp_spaceused)!

Here’s how:

--Select the database to be scanned for table disk usage
USE [DatabaseName]
GO

--Create temporary table 1 - Sizes will be in strings/KB
create table #tmpSizes(
[name] nvarchar(200),
[rows] varchar(50),
reserved varchar(50),
data varchar(50),
index_size varchar(50),
unused varchar(50)
)

--Create temporary table 2 - Sizes will be in KB
create table #tmpSizesFinalKB(
[name] nvarchar(200),
[rows] int,
reserved int,
data int,
index_size int,
unused int
)

--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
select 
[name],
replace([rows],'KB',''),
replace(reserved,'KB',''),
replace(data,'KB',''),
replace(index_size,'KB',''),
replace(unused,'KB','')
from #tmpSizes

--Access the disk usage results (in KB)
select * 
from #tmpSizesFinalKB
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!

 

Strengthen your SQL Server Database Administration Skills – Enroll to our Course!

If you really want to learn sophisticated SQL Server administration techniques, then you should check our on-demand online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
SQL Server Administration Tips (lifetime access – learn more).

Learn More

 

 

Recommended Online Courses:

 

Related SQL Server Administration Articles:

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 3,706