Detect the Amount of Wasted Data Space in a Table for 2000
SP_WASTED_SPACE will run through each column in your database and print a report of all the character columns. It will then print a report with :
* The maximum length that a column is storing
* The average length of data stored in each column
* The amount of wasted space in each column
* Hints on how to improve performance
Create the stored procedure in the Master database. No parameters are needed if you do that since the procedure reads what database you're in. Just attach to the target database and then run SP_WASTED_SPACE
Will compile only in SQL Server 2000.
It may take some time to run on large active databases, so I would recommend running it on a copy of the database.
SET QUOTED_IDENTIFIER OFF
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_WASTED_SPACE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_WASTED_SPACE]
GO
create PROC SP_WASTED_SPACE
as
/*
---------------------------------------------------------------------------
Author: Brian Knight
Date : 3/12/02
Version : 1.0
Update : N/A
Input Parameters : None
Usage : Create in the Master database. Then run SP_WASTED_SPACE when attached
to any database.
---------------------------------------------------------------------------
*/
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
DECLARE @TABLE_NAME varchar(255),
@COLUMN_NAME varchar(255),
@CHARACTER_MAXIMUM_LENGTH int,
@SQL VARCHAR(1000),
@DB_NAME VARCHAR(255)
create table #COLUMN_WASTED (
TABLE_NAME varchar(255),
COLUMN_NAME varchar(255),
DATA_TYPE varchar(10),
CHARACTER_MAXIMUM_LENGTH int,
CHARACTER_USED_LENGTH int,
CHARACTER_AVG_LENGTH int,
CHARACTER_WASTED_LENGTH int
)
SET @DB_NAME = (SELECT db_name())
SET @SQL = "insert into #column_wasted (TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH)"
SET @SQL = @SQL + "(select TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from "+@DB_NAME
SET @SQL = @SQL + ".information_schema.columns where DATA_TYPE in ('varchar', 'nvarchar', 'text',"
SET @SQL = @SQL + "'char', 'nchar') and table_name <> 'dtproperties' and table_name <> 'syssegments')"
--PRINT @SQL
EXEC (@SQL)
DECLARE CUR_COLUMN_WASTED CURSOR
KEYSET
FOR SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH FROM #COLUMN_WASTED
OPEN CUR_COLUMN_WASTED
FETCH NEXT FROM CUR_COLUMN_WASTED INTO @TABLE_NAME, @COLUMN_NAME, @CHARACTER_MAXIMUM_LENGTH
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @SQL = "UPDATE #COLUMN_WASTED
SET CHARACTER_USED_LENGTH = (select ISNULL(max(datalength("+@column_Name+")),0) from "+@table_name+")
WHERE TABLE_NAME = '"+@table_name+"'
AND COLUMN_NAME = '"+@COLUMN_NAME+"'"
EXEC (@SQL)
SET @SQL = "UPDATE #COLUMN_WASTED
SET CHARACTER_AVG_LENGTH = (select ISNULL(avg(datalength("+@column_Name+")),0) from "+@table_name+")
WHERE TABLE_NAME = '"+@table_name+"'
AND COLUMN_NAME = '"+@COLUMN_NAME+"'"
EXEC (@SQL)
END
FETCH NEXT FROM CUR_COLUMN_WASTED INTO @TABLE_NAME, @COLUMN_NAME, @CHARACTER_MAXIMUM_LENGTH
END
CLOSE CUR_COLUMN_WASTED
DEALLOCATE CUR_COLUMN_WASTED
UPDATE #COLUMN_WASTED
SET CHARACTER_WASTED_LENGTH = CHARACTER_MAXIMUM_LENGTH - CHARACTER_USED_LENGTH
SELECT * FROM #COLUMN_WASTED order by TABLE_NAME, COLUMN_NAME
IF (SELECT COUNT(*) FROM #COLUMN_WASTED WHERE DATA_TYPE = 'text') > 0
BEGIN
PRINT 'There are text columns in this database.'
PRINT 'HINT 1: Anything that uses below 8060 characters MAY be able to be converted into a VARCHAR field.'
PRINT 'HINT 2 : Consider placing small text columns to ROWS IN TEXT where the CHARACTER_AVG_LENGTH is less than 250 characters . Here are a list of those columns and their maximum space used:'
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_USED_LENGTH FROM #COLUMN_WASTED WHERE DATA_TYPE = 'text' order by TABLE_NAME, COLUMN_NAME
END
drop table #COLUMN_WASTED
go 
