if exists (select * from sysobjects where id = object_id(N'[dbo].[SP_WASTEDSPACE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_WASTEDSPACE]
GO

SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO

CREATE PROCEDURE SP_WASTEDSPACE @tablename varchar(25), @viewtype varchar(25)= 'compact'  AS

SET NOCOUNT ON
print 'EXECUTING SP_WASTEDSPACE....'
print '--------------------------------------------------------------------------------------'
print 'Revision 10/14/99'
print 'Rev 1.1 Brian Knight - other scripts available (http://www.swynk.com/friends/knight)'
print 'You can view all tables in your database by using "alltableinfo" as you @tablename parameter.'
print '@viewtype = "full" will display the full expanded view'
print 'Only columns with wasted space are displayed.'
print 'If you receive the error "Warning: Null value eliminated from aggregate.",'
print 'then you have mistyped the tablename'
print '---------------------------------------------------------------------------------------'
print ''



DECLARE @fieldname varchar(25),
@lengthfield int,
@dattype varchar(17),
@tabname varchar(25),
@currenttab varchar(25),
@wastedspace int,
@maxspace int,
@wastequery varchar(500)

create table #analyzetable (
fieldname varchar(255),
tabname varchar(255),
lengthfield int,
dattype varchar(255))

if @tablename = 'alltableinfo'
begin
insert into #analyzetable SELECT distinct syscolumns.name, sysobjects.name AS Object,
syscolumns.length, systypes.name AS DatType
FROM sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype
WHERE (sysobjects.xtype = 'U') and sysobjects.status >0 order by sysobjects.name
end
else
begin
insert into #analyzetable SELECT distinct  syscolumns.name, sysobjects.name AS Object,syscolumns.length, systypes.name AS DatType
FROM sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype
WHERE (sysobjects.name = @tablename) and sysobjects.status >0 order by sysobjects.name
end

DECLARE ANALYZECUR  CURSOR scroll keyset FOR
SELECT fieldname, lengthfield, dattype, tabname from #analyzetable


OPEN ANALYZECUR


if @tablename = 'alltableinfo'
begin
FETCH FIRST FROM ANALYZECUR into @fieldname, @lengthfield, @dattype, @tabname set @currenttab = @tabname

print 'Table :  ' + UPPER(@tabname)

--drop table #analyzetable

set @wastedspace = @lengthfield-max(len(@fieldname))
if @wastedspace < 0
begin
set @wastedspace=0
end
if @wastedspace > 0
begin
set @maxspace = max(len(@fieldname))

if @viewtype = 'compact'
begin
print 'Field: ' + @FIELDNAME + ' ' + upper(@dattype) + '(' + rtrim(CONVERT(char(5), @lengthfield))+ ') | Column Using Max : ' + rtrim(convert(char(5),@maxspace)) + ' |  Wasted Space: ' +rtrim(convert(char(5),@wastedspace)) + ' |'
end
if @viewtype = 'full'
begin
select @fieldname as Field, max(len(@fieldname)) as MaxUsed, upper(@dattype ) as DataType, @lengthfield as Allocated, @wastedspace as WastedSpace
END
END 

WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM ANALYZECUR into @fieldname, @lengthfield, @dattype, @tabname
if @@FETCH_STATUS=0
BEGIN
set @maxspace = max(len(@fieldname))
	if @currenttab=@tabname
	begin
		set @wastedspace = @lengthfield-max(len(@fieldname))
		if @wastedspace < 0
		begin
		set @wastedspace=0
		end
if @wastedspace > 0
begin
if @viewtype = 'compact'
begin
print 'Field: ' + @FIELDNAME + ' ' + upper(@dattype) + '(' + rtrim(CONVERT(char(5), @lengthfield))+ ') | Column Using Max : ' + rtrim(convert(char(5),@maxspace)) + ' |  Wasted Space: ' +rtrim(convert(char(5),@wastedspace)) + ' | ' 
end
if @viewtype = 'full'
begin
select @fieldname, max(len(@fieldname)),upper(@dattype), @lengthfield,  @wastedspace
end
end
end
else
begin
print ''
print 'Table :  ' + UPPER(@tabname)

set @wastedspace = @lengthfield-max(len(@fieldname))
		if @wastedspace < 0
		begin
		set @wastedspace=0
	end
if @wastedspace > 0
begin
if @viewtype = 'compact'
begin
print 'Field: ' + @FIELDNAME + ' ' + upper(@dattype) + '(' + rtrim(CONVERT(char(5), @lengthfield))+ ') | Column Using Max : ' + rtrim(convert(char(5),@maxspace)) + ' |  Wasted Space: ' +rtrim(convert(char(5),@wastedspace)) + ' | ' 
end
if @viewtype = 'full'
begin
select @fieldname as Field, max(len(@fieldname)) as MaxUsed, upper(@dattype) as DataType, @lengthfield as Allocated, @lengthfield-max(len(@fieldname)) as WastedSpace
END
END
end
set @currenttab = @tabname
END
END
END

ELSE
BEGIN
FETCH FIRST FROM ANALYZECUR into @fieldname, @lengthfield, @dattype, @tabname
set @maxspace = max(len(@fieldname))
set @wastedspace = @lengthfield-max(len(@fieldname))
if @wastedspace < 0
begin
set @wastedspace=0
end

print 'Table :  ' + UPPER(@tabname)
if @wastedspace > 0
begin
if @viewtype = 'compact'
begin

print 'Field: ' + @FIELDNAME + ' ' + upper(@dattype) + '(' + rtrim(CONVERT(char(5), @lengthfield))+ ') | Column Using Max : ' + rtrim(convert(char(5),@maxspace)) + ' |  Wasted Space: ' +rtrim(convert(char(5),@wastedspace)) + ' | ' 
END
if @viewtype = 'full'
begin
select @fieldname as Field, max(len(@fieldname)) as MaxUsed, upper(@dattype ) as DataType, @lengthfield as Allocated, @wastedspace as WastedSpace
END
END
WHILE @@FETCH_STATUS=0
BEGIN
if @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM ANALYZECUR into @fieldname, @lengthfield, @dattype, @tabname
set @maxspace = max(len(@fieldname))
set @wastedspace = @lengthfield-max(len(@fieldname))
if @wastedspace < 0
begin
set @wastedspace=0
end
if @wastedspace > 0
begin

if @viewtype = 'compact'
begin
print 'Field: ' + @FIELDNAME + ' ' + upper(@dattype) + '(' + rtrim(CONVERT(char(5), @lengthfield))+ ') | Column Using Max : ' + rtrim(convert(char(5),@maxspace)) + ' |  Wasted Space: ' +rtrim(convert(char(5),@wastedspace)) + ' | ' 
END
if @viewtype = 'full'
begin
select @fieldname, max(len(@fieldname)), upper(@dattype), @lengthfield, @wastedspace
END
END
END
END
END

close ANALYZECUR
deallocate ANALYZECUR

--Clean Up
drop table #analyzetable
SET NOCOUNT OFF
print ''
print '                    -------------------Query Complete!-------------------'




GO
SET QUOTED_IDENTIFIER  OFF    SET ANSI_NULLS  ON 
GO