Get Data Statistics For All Tables

				
--**************************************
-- Name: Get Data Statistics For All Tables
-- Description:These procedures will help in analyzing your data. They will look at all tables owned by the given user within the given database. Each column in the table will be analyzed, if the column contains less than 20 unique values the spread of the data will be returned, if it contains more than 20 unique values the most common 20 will be returned.
The data can optionally be output to a table for further analysis.
A separate version of the script has been included for SQL2005.
-- By: Tim Watts
--
-- Inputs:@DBName varchar(255)-- Database
@OwnerName varchar(255)-- Object owner
@IncludeColTotals bit = 0 -- Total up the Frequency for each column
@KeepResultsTable bit = 0 -- Save the results to DBName.Owner.DBName_Metrix
--
-- Assumes:Two different versions of the script are provided, one for SQL2000 and earlier, the other for SQL2005.
Columns of data type image, text or ntext will not be analyzed since they cannot be used by the GROUP BY statement.
--
--This code is copyrighted and has-- limited warranties.Please see http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=1187&lngWId=5--for details.--**************************************

/*
Full syntax is:
exec master..SP_GetDBMetrix 'DBName', 'Owner', includeTotals, keepTable
The last two parameters are optional and will default to false.
For example:
exec master..SP_GetDBMetrix 'Northwind', 'dbo', 1, 1
If the includeTotals flag is set a row will be added totalling the 'frequency' for each column. This will show at a glance if it includes all values in the table or only the 20 most used (although due to rounding it won't always add up to 100).
If the keepTable flag is set a table will be created in the specific database called DBName_Metrix. It will be dropped if it previously existed.
*/
USE Master
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_GetColumnValueSpread]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SP_GetColumnValueSpread
GO
CREATE PROCEDURE dbo.SP_GetColumnValueSpread
(@DBName varchar(255), 
@Owner varchar(255), 
@TableName varchar(255), 
@ColumnName varchar(255))
AS
/*******************************************************************************
 Created: September 2007		Tim Watts
 Get the spread of values within the given column of the table
*******************************************************************************/
-- NOTE: There may be other errors displayed but they don't matter since any
-- columns which cannot be analysed will just be ignored anyway.
DECLARE @UniqueValues INT, @RecordCount INT, @Top varchar(10)
DECLARE @TableParsed varchar(255)
SELECT @TableParsed = PARSENAME(@TableName, 1)
-- get a return value from an EXEC statement, example got from:
-- http://www.databasejournal.com/features/mssql/article.php/1458831
CREATE TABLE #return (code INT NULL)
INSERT INTO #return EXEC ('SELECT count(1) FROM ' + @DBName + '.' + @TableName + ' (nolock)')
SELECT @RecordCount = isnull(code,0) from #return
TRUNCATE TABLE #return-- Will use it again below
INSERT INTO #return EXEC ('SELECT COUNT(distinct(' + @ColumnName + ')) FROM ' + @DBName + '.' + @TableName + ' (nolock)')
SELECT @UniqueValues = isnull(code,0) FROM #return
DROP TABLE #return-- don't need it anymore
-- If every value in the column is unique (for example if it's a lookup table), don't return it
IF @UniqueValues <> @RecordCount
BEGIN
-- If there are more than 20 different values just return the 20 which occur the most often
IF (@UniqueValues > 20)
BEGIN
SET @Top = 'TOP 20 '
END
INSERT INTO #output exec ('SELECT ' + @Top + '''' + @DBName + ''', ''' + @Owner + ''', ''' + @TableParsed + ''', ''' + @ColumnName + ''', Convert(varchar(255), ' + @ColumnName + '), convert(int, count(*)), convert(decimal(5,2), convert(decimal(13,4), count(*)) / convert(decimal(13,4), ' + @RecordCount + ') * 100) FROM ' + @DBName + '.' + @TableName + ' (nolock) GROUP BY ' + @ColumnName + ' ORDER BY COUNT(*) DESC')
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_GetTableMetrix]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SP_GetTableMetrix
GO
CREATE PROCEDURE dbo.SP_GetTableMetrix
(@DBName varchar(255), 
@OwnerName varchar(255), 
@TableName varchar(255), 
@IncludeColTotals BIT = 0)
AS
/*******************************************************************************
 Created: September 2007		Tim Watts
 Loop round each column of the table
*******************************************************************************/
DECLARE @ColName varchar(255)
DECLARE @TableParsed varchar(255)
SELECT @TableParsed = PARSENAME(@TableName, 1)
-- Don't check the table which the procedure creates itself
IF @TableParsed <> @DBName + '_Metrix'
BEGIN
-- Don't get image, text or ntext columns, they're invalid data types for GROUP BY which we'll do later
DECLARE ColCursor CURSOR FOR SELECT name FROM SYSCOLUMNS WHERE ID = OBJECT_ID(@TableName) and xtype NOT IN (34, 35, 99)
OPEN ColCursor
FETCH NEXT FROM ColCursor INTO @ColName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Get the frequency of values for the current column
exec SP_GetColumnValueSpread @DBName, @OwnerName, @TableName, @ColName
-- Including the totals for each column will show if the values shown are only the TOP 20
-- as the total will be less than 100 (although it often will be just under due to rounding errors)
IF @IncludeColTotals = 1
INSERT INTO #output EXEC ('SELECT ''' + @DBName + ''', ''' + @OwnerName + ''',''' + @TableParsed + ''', ''' + @ColName + ''', ''_MetrixTotal'', sum(RecordCount), sum(Frequency) FROM #output WHERE TableName = ''' + @TableParsed + ''' AND ColumnName = ''' + @ColName + '''')
FETCH NEXT FROM ColCursor INTO @ColName
END
CLOSE ColCursor
DEALLOCATE ColCursor
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_GetDBMetrix]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SP_GetDBMetrix
GO
CREATE PROCEDURE dbo.SP_GetDBMetrix
(@DBName varchar(255), -- Database
@OwnerName varchar(255), -- Object owner
@IncludeColTotals bit = 0,-- Total up the Frequency for each column
@KeepResultsTable bit = 0)-- Save the results to DBName.Owner.DBName_Metrix
AS
/*******************************************************************************
 Created: September 2007		Tim Watts
 Main controlling procedure, calls SP_GetTableMetrix for each table 
 in the database.
*******************************************************************************/
-- Don't bother displaying warnings about NULLs
SET ANSI_WARNINGS OFF
DECLARE @cmd1 nvarchar(2000)
-- Create a temp table to gather the output
CREATE TABLE #output (DBName varchar(255), OwnerName varchar(255), TableName varchar(255), ColumnName varchar(255), Value varchar(255), RecordCount int, Frequency decimal(13, 4))
SELECT @cmd1 = 'EXEC SP_GetTableMetrix ''''' + @DBName + ''''', ''''' + @OwnerName + ''''', ''''?'''', ' + convert(varchar(1), @IncludeColTotals)
-- Get the table Metrix for each table in the database passed
EXEC ('USE ' + @DBName + ' EXEC SP_MSForEachTable @command1=''' + @cmd1 + '''')
-- Clean up the extra _MetrixTotal rows
SET NOCOUNT ON
DELETE FROM #output WHERE Value = '_MetrixTotal' AND RecordCount IS NULL
SET NOCOUNT OFF
IF @KeepResultsTable = 1
BEGIN
EXEC ('USE ' + @DBName + ' IF EXISTS (select * from dbo.sysobjects where id = object_id(N''[' + @OwnerName + '].[' + @DBName + '_Metrix]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) drop table [' + @OwnerName + '].[' + @DBName + '_Metrix]')
EXEC ('USE ' + @DBName + ' select * INTO [' + @OwnerName + '].[' + @DBName + '_Metrix] FROM #output')
PRINT 'Metrix saved to ' + @DBName + '.' + @OwnerName + '.' + @DBName + '_Metrix'
END
ELSE
SELECT * FROM #output ORDER BY TableName
DROP TABLE #output
SET ANSI_WARNINGS ON
GO
/*
SQL 2005 Version
Full syntax is:
exec master..SP_SQL2005GetDBMetrix 'DBName', 'Owner', includeTotals, keepTable
The last two parameters are optional and will default to false.
For example:
exec master..SP_SQL2005GetDBMetrix 'Northwind', 'dbo', 1, 1
If the includeTotals flag is set a row will be added totalling the 'frequency' for each column. This will show at a glance if it includes all values in the table or only the 20 most used (although due to rounding it won't always add up to 100).
If the keepTable flag is set a table will be created in the specific database called DBName_Metrix. It will be dropped if it previously existed.
*/
use master
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_SQL2005GetColumnValueSpread]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SP_SQL2005GetColumnValueSpread
GO
CREATE PROCEDURE dbo.SP_SQL2005GetColumnValueSpread
(@DBName varchar(255), 
@Owner varchar(255), 
@TableName varchar(255), 
@ColumnName varchar(255))
AS
/*******************************************************************************
 Created: September 2007		Tim Watts
 Get the spread of values within the given column of the table
*******************************************************************************/
-- NOTE: There may be other errors displayed but they don't matter since any
-- columns which cannot be analysed will just be ignored anyway.
DECLARE @UniqueValues INT, @RecordCount INT, @Top varchar(10)
DECLARE @TableParsed varchar(255)
SELECT @TableParsed = PARSENAME(@TableName, 1)
-- get a return value from an EXEC statement, example got from:
-- http://www.databasejournal.com/features/mssql/article.php/1458831
CREATE TABLE #return (code INT NULL)
INSERT INTO #return EXEC ('SELECT count(1) FROM ' + @DBName + '.' + @Owner + '.' + @TableName + ' (nolock)')
SELECT @RecordCount = isnull(code,0) from #return
TRUNCATE TABLE #return-- Will use it again below
INSERT INTO #return EXEC ('SELECT COUNT(distinct(' + @ColumnName + ')) FROM ' + @DBName + '.' + @Owner + '.' + @TableName + ' (nolock)')
SELECT @UniqueValues = isnull(code,0) FROM #return
DROP TABLE #return-- don't need it anymore
-- If every value in the column is unique (for example if it's a lookup table), don't return it
IF @UniqueValues <> @RecordCount
BEGIN
-- If there are more than 20 different values just return the 20 which occur the most often
IF (@UniqueValues > 20)
BEGIN
SET @Top = 'TOP 20 '
END
INSERT INTO #output exec ('SELECT ' + @Top + '''' + @DBName + ''', ''' + @Owner + ''', ''' + @TableParsed + ''', ''' + @ColumnName + ''', Convert(varchar(255), ' + @ColumnName + '), convert(int, count(*)), convert(decimal(5,2), convert(decimal(13,4), count(*)) / convert(decimal(13,4), ' + @RecordCount + ') * 100) FROM ' + @DBName + '.' + @Owner + '.' + @TableName + ' (nolock) GROUP BY ' + @ColumnName + ' ORDER BY COUNT(*) DESC')
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_SQL2005GetTableMetrix]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SP_SQL2005GetTableMetrix
GO
CREATE PROCEDURE dbo.SP_SQL2005GetTableMetrix
(@DBName varchar(255), 
@OwnerName varchar(255), 
@TableName varchar(255), 
@IncludeColTotals BIT = 0)
AS
/*******************************************************************************
 Created: September 2007		Tim Watts
 Loop round each column of the table
*******************************************************************************/
DECLARE @ColName varchar(255)
DECLARE @TableParsed varchar(255)
DECLARE @FullTableName varchar(255)
DECLARE @CursorSQL varchar(1500)
SELECT @TableParsed = PARSENAME(@TableName, 1)
select @FullTableName = @DBName + '.' + @TableName
-- Don't check the table which the procedure creates itself
IF @TableParsed <> @DBName + '_Metrix'
BEGIN
-- Don't get image, text or ntext columns, they're invalid data types for GROUP BY which we'll do later
	SELECT @CursorSQL = 'DECLARE ColCursor CURSOR FOR SELECT name FROM ' + @DBName + '.sys.syscolumns WHERE ID = OBJECT_ID(''' + @FullTableName + ''') and xtype NOT IN (34, 35, 99)'
	EXEC (@CursorSQL)
OPEN ColCursor
FETCH NEXT FROM ColCursor INTO @ColName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Get the frequency of values for the current column
exec master.dbo.SP_SQL2005GetColumnValueSpread @DBName, @OwnerName, @TableParsed, @ColName
-- Including the totals for each column will show if the values shown are only the TOP 20
-- as the total will be less than 100 (although it often will be just under due to rounding errors)
IF @IncludeColTotals = 1
INSERT INTO #output EXEC ('SELECT ''' + @DBName + ''', ''' + @OwnerName + ''',''' + @TableParsed + ''', ''' + @ColName + ''', ''_MetrixTotal'', sum(RecordCount), sum(Frequency) FROM #output WHERE TableName = ''' + @TableParsed + ''' AND ColumnName = ''' + @ColName + '''')
FETCH NEXT FROM ColCursor INTO @ColName
END
CLOSE ColCursor
DEALLOCATE ColCursor
END
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_SQL2005GetDBMetrix]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.SP_SQL2005GetDBMetrix
GO
CREATE PROCEDURE dbo.SP_SQL2005GetDBMetrix
(@DBName varchar(255), -- Database
@OwnerName varchar(255), -- Object owner
@IncludeColTotals bit = 0,-- Total up the Frequency for each column
@KeepResultsTable bit = 0)-- Save the results to DBName.Owner.DBName_Metrix
AS
/*******************************************************************************
 Created: September 2007		Tim Watts
 Main controlling procedure, calls SP_SQL2005GetTableMetrix for each table 
 in the database.
*******************************************************************************/
-- Don't bother displaying warnings about NULLs
SET ANSI_WARNINGS OFF
DECLARE @cmd1 nvarchar(2000)
-- Create a temp table to gather the output
CREATE TABLE #output (DBName varchar(255), OwnerName varchar(255), TableName varchar(255), ColumnName varchar(255), Value varchar(255), RecordCount int, Frequency decimal(13, 4))
SELECT @cmd1 = 'EXEC master.dbo.SP_SQL2005GetTableMetrix ''''' + @DBName + ''''', ''''' + @OwnerName + ''''', ''''?'''', ' + convert(varchar(1), @IncludeColTotals)
-- Get the table Metrix for each table in the database passed
EXEC ('USE ' + @DBName + ' EXEC sys.sp_MSforeachtable @command1=''' + @cmd1 + '''')
-- Clean up the extra _MetrixTotal rows
SET NOCOUNT ON
DELETE FROM #output WHERE Value = '_MetrixTotal' AND RecordCount IS NULL
SET NOCOUNT OFF
IF @KeepResultsTable = 1
BEGIN
EXEC ('USE ' + @DBName + ' IF EXISTS (select * from dbo.sysobjects where id = object_id(N''[' + @OwnerName + '].[' + @DBName + '_Metrix]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) drop table [' + @OwnerName + '].[' + @DBName + '_Metrix]')
EXEC ('USE ' + @DBName + ' select * INTO [' + @OwnerName + '].[' + @DBName + '_Metrix] FROM #output')
PRINT 'Metrix saved to ' + @DBName + '.' + @OwnerName + '.' + @DBName + '_Metrix'
END
ELSE
SELECT * FROM #output ORDER BY TableName
DROP TABLE #output
SET ANSI_WARNINGS ON
GO