One of my favourite SQL Routines

Looking through a few of the sql routines I wrote within the past year, I dug this one up because I want to write a version which works with Oracle. The following bit of code will go through all view/tables within the current database. It will report in one table the following combination:

TableName
ColumnName
TotalRowCount
DistinctCount
NullCount

The only limitation this script has is the limitation on dynamic sql (8000 characters). I get around this a little bit by using temporary procedures. I have avoided using cursors to keep it simple and efficient.

/*

ANALYZE TABLES/VIEWS in current database by rowcount, distinct count

and null count.

August 8, 2006 Initial Version

COPYRIGHT © 2006 George Zabanah

Please feel free to use this code and distribute,

but leave this header at the top.

No warranty of any kind is implied by using this code.

Use at your own risk.

*/

-- Table to hold column information

CREATE TABLE #ColumnData (TableName sysname,

ColumnName sysname,

TOTALROWS bigint,

DistinctCount bigint,

NullCount bigint)

GO

-- Temp procedure for a particular table, column combination

CREATE PROCEDURE #C (@TableName sysname, @ColumnName sysname, @CastVarchar bit = 0)

AS

declare @mysql varchar(4000)

set @mysql = 'insert #ColumnData select ''' + @TableName + ''' TABLENAME, ''' +

@COLUMNNAME + ''' COLUMNNAME, COUNT(*) TOTALROWS, ' +

'COUNT(DISTINCT(' + (CASE WHEN @CastVarchar = 1 then 'CAST(' else '' end) +

'[' + @COLUMNNAME + ']' +

(CASE WHEN @CastVarchar = 1 then ' as varchar(8000))' else '' end) +

')) DISTINCTCOUNT, SUM(CASE WHEN [' +

@COLUMNNAME + '] is NULL THEN 1 ELSE 0 END) NULLCOUNT from [' + @TableName + ']'

RAISERROR('%s.%s',10,1,@TableName,@ColumnName) WITH NOWAIT

exec(@mysql)

GO

-- Temp procedure for a particular table

CREATE PROCEDURE #T (@TableName sysname)

AS

declare @mysql varchar(4000)

set @mysql = 'declare @t varchar(8000) set @t = ''' + @TableName + ''''

select @mysql = @mysql COLLATE DATABASE_DEFAULT + ' exec #C @t,''' + COLUMN_NAME + '''' +

(CASE WHEN DATA_TYPE like '%Text' then ',1' else '' end)

from information_schema.columns where table_name = @TableName

RAISERROR('-- TABLE: %s --',10,1,@TableName) WITH NOWAIT

exec('SET NOCOUNT ON ' + @mysql + ' SET NOCOUNT OFF')

GO

Declare @mysql varchar(4000)

-- TABLES

set @mysql = ''

select @mysql = @mysql + ' exec #T ''' + table_name + ''''

from information_schema.tables where table_type = 'BASE TABLE'

exec(@mysql)

-- VIEWS

set @mysql = ''

select @mysql = @mysql + ' exec #T ''' + table_name + ''''

from information_schema.tables where table_type = 'VIEW'

exec(@mysql)

select * from #ColumnData

drop table #ColumnData

drop procedure #T

drop procedure #C

A few noteworthy points:

• the #T temporary procedure iterates through each table/view and the #C temporary procedure iterates through the columns of each table/view.
• I use the simple set-based variable assignment in SQL to concatenate one big dynamic SQL statement for each table/view.

Like I said before, this script is one of my favourites.