(DIR) Return Create A Forum - Home
---------------------------------------------------------
ExcelSoft Database Professionals
(HTM) https://esdbp.createaforum.com
---------------------------------------------------------
*****************************************************
(DIR) Return to: Scripts
*****************************************************
#Post#: 159--------------------------------------------------
Library SP to find wasted space in each column of a table
By: srinivasma_exceldbp Date: June 24, 2015, 10:49 pm
---------------------------------------------------------
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF OBJECT_ID('sp_lib_findWastedSpace') IS NOT NULL DROP PROC
sp_lib_findWastedSpace
GO
/***************************************************************
*******************************************
NAME
SYNOPSIS
table
AUTHOR : M A Srinivas
Usage
'AdventureWorks2012','Sales.SalesOrderDetail', 10,'GB',1
'
****************************************************************
******************************************/
Create Procedure dbo.sp_lib_findWastedSpace
/* Declare Parameters */
@databaseName sysname = 'AdventureWorks2012'
, @tableName sysname = 'Sales.SalesOrderDetail'
, @percentGrowth tinyint = 10 /* allow for up to
10% growth by default */
, @displayUnit char(2) = 'GB' /* KB, MB, GB, or TB
*/
, @debug bit = 1
As
Set NoCount On;
Set XACT_Abort On;
Set Ansi_Padding On;
Set Ansi_Warnings On;
Set ArithAbort On;
Set Concat_Null_Yields_Null On;
Set Numeric_RoundAbort Off;
Begin
/* Make sure our environment is clean and ready to go */
If Exists(Select object_id From tempdb.sys.tables Where name
= '##values')
Drop Table ##values;
If Exists(Select object_id From tempdb.sys.tables Where name
= '##definition')
Drop Table ##definition;
If Exists(Select object_id From tempdb.sys.tables Where name
= '##spaceRequired')
Drop Table ##spaceRequired;
If Exists(Select object_id From tempdb.sys.tables Where name
= '##results')
Drop Table ##results;
/* Declare Variables */
Declare @sqlStatement_getColumnList nvarchar(max)
, @sqlStatement_values nvarchar(max)
, @sqlStatement_columns nvarchar(max)
, @sqlStatement_tableDefinition1 nvarchar(max)
, @sqlStatement_tableDefinition2 nvarchar(max)
, @sqlStatement_tableDefinition3 nvarchar(max)
, @sqlStatement_spaceRequired nvarchar(max)
, @sqlStatement_results nvarchar(max)
, @sqlStatement_displayResults nvarchar(max)
, @sqlStatement_total nvarchar(max)
, @currentRecord int
, @growthPercentage float;
Declare @columnList Table
(
id int identity(1,1)
, table_id int
, columnName varchar(128)
, user_type_id tinyint
, max_length smallint
, columnStatus tinyint
);
/* Initialize variables
I'm doing it this way to support 2005 environments,
too */
Select @sqlStatement_tableDefinition1 = ''
, @sqlStatement_tableDefinition2 = ''
, @sqlStatement_tableDefinition3 = ''
, @sqlStatement_spaceRequired = 'Select '
, @sqlStatement_results = 'Select '
, @sqlStatement_displayResults = ''
, @sqlStatement_total = 'Select ''Total'',
Null, '
, @sqlStatement_values = 'Select '
, @sqlStatement_columns = 'Select '
, @growthPercentage =
1+(@percentGrowth/100.0);
Set @sqlStatement_getColumnList = '
Select c.object_id As [table_id]
, c.name
, t.user_type_id
, c.max_length
, 0 /* not yet columnStatus */
From ' + @databaseName + '.sys.columns As c
Join ' + @databaseName + '.sys.types As t
On c.user_type_id = t.user_type_id
Where c.object_id = IsNull(Object_Id(''' + @databaseName +
'.' + @tableName + '''), c.object_id)
And t.user_type_id In (48, 52, 56, 127, 167, 175, 231,
239);'
If @Debug = 1
Begin
Select @sqlStatement_getColumnList;
End;
Insert Into @columnList
Execute sp_executeSQL @sqlStatement_getColumnList;
If @Debug = 1
Begin
Select * From @columnList;
End;
/* Begin our loop. We're going to run through this for
every column. */
While Exists(Select * From @columnList Where columnStatus =
0)
Begin
/* Grab a column that hasn't been processed yet */
Select Top 1 @currentRecord = id
From @columnList
Where columnStatus = 0
Order By id;
/* First, let's build the statement we're going to use
to get our min/max values */
Select @sqlStatement_values = @sqlStatement_values +
Case When user_type_id In (48, 52, 56, 127)
Then 'Max(' + columnName + ') As [' +
columnName + '], '
+ 'Min(' + columnName + ') As [min' +
columnName + '], '
Else 'Max(Len(' + columnName + ')) As [' +
columnName + '], '
+ 'Avg(Len(' + columnName + ')) As [avg' +
columnName + '], '
End
From @columnList
Where id = @currentRecord;
/* Next, let's build the statement that's going to show
us how much space the column is currently consuming */
Select @sqlStatement_columns = @sqlStatement_columns
+ Case When user_type_id = 48 Then '1' -- tinyint
When user_type_id = 52 Then '2' -- smallint
When user_type_id = 56 Then '4' -- int
When user_type_id = 127 Then '8' -- bigint
When user_type_id In (167, 175) Then
Cast(max_length As varchar(10))-- varchar or char
Else Cast(max_length * 2 As varchar(10)) --
nvarchar or nchar
--Else '0'
End + ' As [' + columnName + '], '
From @columnList
Where id = @currentRecord;
/* This section is used to build a table definition */
Select @sqlStatement_tableDefinition1 =
@sqlStatement_tableDefinition1 + '[' + columnName + '] '
+ Case
When user_type_id = 48 Then 'tinyint'
When user_type_id = 52 Then 'smallint'
When user_type_id = 56 Then 'int'
When user_type_id = 127 Then 'bigint'
Else 'smallint'
End + ', '
+ Case When user_type_id In (48, 52, 56, 127) Then
'[min' Else '[avg' End + columnName + '] '
+ Case
When user_type_id = 48 Then 'tinyint'
When user_type_id = 52 Then 'smallint'
When user_type_id = 56 Then 'int'
When user_type_id = 127 Then 'bigint'
Else 'smallint'
End + ', '
From @columnList
Where id = @currentRecord;
/* More dynamic table definition code */
Select @sqlStatement_tableDefinition2 =
@sqlStatement_tableDefinition2 + '[' + columnName + '] '
+ Case
When user_type_id = 48 Then 'tinyint'
When user_type_id = 52 Then 'smallint'
When user_type_id = 56 Then 'int'
When user_type_id = 127 Then 'bigint'
Else 'smallint'
End + ', '
From @columnList
Where id = @currentRecord;
/* And yet more dynamic table definition code */
Select @sqlStatement_tableDefinition3 =
@sqlStatement_tableDefinition3 + columnName + ' smallint, '
+ columnName
+ '_bytes bigint, '
From @columnList
Where id = @currentRecord;
/* This is where we see how much space we actually need,
based on our min/max values.
This is where we consider the % of growth that we
expect to see in a reasonable period of time. */
Select @sqlStatement_spaceRequired =
@sqlStatement_spaceRequired +
Case When user_type_id In (48, 52, 56, 127)
Then 'Case When ([' + columnName + '] * ' +
Cast(@growthPercentage As varchar(5)) + ') <= 255
And [min' + columnName + '] >= 0
Then 1
When ([' + columnName + '] * ' +
Cast(@growthPercentage As varchar(5)) + ') <= 32768
And [min' + columnName + '] >=
-32768
Then 2
When ([' + columnName + '] * ' +
Cast(@growthPercentage As varchar(5)) + ') <= 2147483647
And [min' + columnName + '] >=
-2147483647
Then 4
Else 8 End '
Else columnName
End + ' As [' + columnName + '], '
From @columnList
Where id = @currentRecord;
/* This is where the analysis occurs to tell us how much
space we're potentially wasting */
Select @sqlStatement_results = @sqlStatement_results +
'd.[' + columnName + '] - sr.[' + columnName
+ '] As [' + columnName + '], ' +
'(d.[' + columnName + '] - sr.[' +
columnName + ']) * rowCnt As [bytes], '
From @columnList
Where id = @currentRecord;
/* This is where we get our pretty results table from */
Select @sqlStatement_displayResults =
@sqlStatement_displayResults + 'Select ''' + columnName + ''' As
[columnName] '
+ ', ' +
columnName + ' As [byteReduction] '
-- + ', ' +
columnName + '_bytes As [estimatedSpaceSavings] '
+ ', ' +
columnName + '_bytes / 1024.0 / 1024.0 As
[estimatedSpaceSavings] '
+ ' From
##results'
+ ' Union All '
From @columnList
Where id = @currentRecord;
/* And lastly, this is where we get our total from */
Select @sqlStatement_total = @sqlStatement_total +
'([' + columnName + '_bytes] / 1024.0 / 1024.0) + '
From @columnList
Where id = @currentRecord;
/* Mark the column as processed so we can move on to the
next one */
Update @columnList
Set columnStatus = 1
Where id = @currentRecord;
End;
Select @sqlStatement_values = @sqlStatement_values + '
Count(*) As [rowCnt], 1 As [id] From ' + @databaseName + '.' +
@tableName + ' Option (MaxDop 1);'
, @sqlStatement_columns = @sqlStatement_columns + ' ' +
Cast(@currentRecord As varchar(4)) + ' As [columnCnt], 1 As
[id];';
Set @sqlStatement_tableDefinition1 = 'Create Table
##values('
+
@sqlStatement_tableDefinition1
+ ' rowCnt bigint, id
tinyint)';
Set @sqlStatement_tableDefinition2 = 'Create Table
##definition('
+
@sqlStatement_tableDefinition2
+ ' columnCnt bigint, id
tinyint)';
Set @sqlStatement_tableDefinition3 = 'Create Table
##results('
+
@sqlStatement_tableDefinition3
+ ' id tinyint)';
Set @sqlStatement_spaceRequired =
@sqlStatement_spaceRequired + '1 As [id] Into ##spaceRequired
From ##values;'
Set @sqlStatement_results = @sqlStatement_results + '1 As
[id] From ##definition As d Join ##spaceRequired As sr On d.id =
sr.id Join ##values As v On d.id = v.id;'
Set @sqlStatement_displayResults =
@sqlStatement_displayResults + @sqlStatement_total + '0 From
##results';
/* Print our dynamic SQL statements in case we need to
troubleshoot */
If @debug = 1
Begin
Select @sqlStatement_values As '@sqlStatement_values'
, @sqlStatement_columns As '@sqlStatement_columns'
, @sqlStatement_tableDefinition1 As
'@sqlStatement_tableDefinition1'
, @sqlStatement_tableDefinition2 As
'@sqlStatement_tableDefinition2'
, @sqlStatement_spaceRequired As
'@sqlStatement_spaceRequired'
, @sqlStatement_results As '@sqlStatement_results'
, @sqlStatement_displayResults As
'@sqlStatement_displayResults'
, @sqlStatement_total As '@sqlStatement_total';
End;
Select @sqlStatement_tableDefinition1 As 'Table Definition
1';
Execute sp_executeSQL @sqlStatement_tableDefinition1;
Select @sqlStatement_tableDefinition2 As 'Table Definition
2';
Execute sp_executeSQL @sqlStatement_tableDefinition2;
Select @sqlStatement_tableDefinition3 As 'Table Definition
3';
Execute sp_executeSQL @sqlStatement_tableDefinition3;
Select @sqlStatement_values As 'Insert 1';
Insert Into ##values
Execute sp_executeSQL @sqlStatement_values;
Select @sqlStatement_columns As 'Insert 2';
Insert Into ##definition
Execute sp_executeSQL @sqlStatement_columns;
Select @sqlStatement_spaceRequired As 'Execute space
required';
Execute sp_executeSQL @sqlStatement_spaceRequired;
Select @sqlStatement_results As 'Execute results';
Insert Into ##results
Execute sp_executeSQL @sqlStatement_results;
/* Output our table values for troubleshooting purposes */
If @debug = 1
Begin
Select 'definition' As 'tableType', * From ##definition
y
Select 'values' As 'tableType', * from ##values x
Select 'spaceRequired' As 'tableType', * From
##spaceRequired;
Select 'results' As 'tableType', * From ##results;
End;
Select @sqlStatement_displayResults As 'Final results';
Execute sp_executeSQL @sqlStatement_displayResults;
/* Clean up our mess */
--Drop Table ##values;
--Drop Table ##definition;
--Drop Table ##spaceRequired;
--Drop Table ##results;
Set NoCount Off;
Return 0;
End
Go
exec sp_ms_marksystemobject 'sp_lib_findWastedSpace'
go
grant exec on sp_lib_findWastedSpace to public
go
*****************************************************