May 8, 2009 at 8:36 am
Colleagues from another department asked me if I’ve been able to get the number of fields that they actually use from one custom database (not the number of fields per table, which I could get from the data dictionary). Is there a reporting tool in SQL that could determine this? I know if I actually open table, I can see if field is empty or not. But that is not the best way, since there are a lot of tables in that database.
Brano
May 8, 2009 at 9:48 pm
This script will find all the fields in the database that allow NULL values and then give you the number of rows in the table and the number of NULL values in the field and the percentage of NULL values. If the field has never been used, the NULLCOUNT will equal the TABLECOUNT.
DECLARE @sql VARCHAR(MAX)
CREATE TABLE #TMP (
CLMN VARCHAR(500),
NULLCOUNT INT,
DATATYPE VARCHAR(50),
TABLECOUNT INT)
SELECT @sql = COALESCE(@SQL,'') + CAST('INSERT INTO #TMP Select ''' + TABLE_SCHEMA + '.' + REPLACE(TABLE_NAME,'''','''''') + '.' + COLUMN_NAME + ''' AS Clmn, count(*) NullCount, ''' + DATA_TYPE + ''', (Select count(*) FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']) AS TableCount FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] IS NULL ;' + CHAR(13) AS VARCHAR(MAX))
FROM INFORMATION_SCHEMA.COLUMNS
JOIN sysobjects B
ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = B.NAME
WHERE IS_NULLABLE = 'YES' AND XTYPE = 'U'
EXEC( @sql)
SELECT A.CLMN,
A.NULLCOUNT,
A.TABLECOUNT,
A.DATATYPE,
(A.NULLCOUNT * 1.0 / A.TABLECOUNT) NULLPERCENT
FROM #TMP A
WHERE A.NULLCOUNT > 0
ORDER BY NULLPERCENT DESC
DROP TABLE #TMP
May 11, 2009 at 8:24 am
Ken Simmons (5/8/2009)
This script will find all the fields in the database that allow NULL values and then give you the number of rows in the table and the number of NULL values in the field and the percentage of NULL values. If the field has never been used, the NULLCOUNT will equal the TABLECOUNT.
DECLARE @sql VARCHAR(MAX)
CREATE TABLE #TMP (
CLMN VARCHAR(500),
NULLCOUNT INT,
DATATYPE VARCHAR(50),
TABLECOUNT INT)
SELECT @sql = COALESCE(@SQL,'') + CAST('INSERT INTO #TMP Select ''' + TABLE_SCHEMA + '.' + REPLACE(TABLE_NAME,'''','''''') + '.' + COLUMN_NAME + ''' AS Clmn, count(*) NullCount, ''' + DATA_TYPE + ''', (Select count(*) FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']) AS TableCount FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] WHERE [' + COLUMN_NAME + '] IS NULL ;' + CHAR(13) AS VARCHAR(MAX))
FROM INFORMATION_SCHEMA.COLUMNS
JOIN sysobjects B
ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = B.NAME
WHERE IS_NULLABLE = 'YES' AND XTYPE = 'U'
EXEC( @sql)
SELECT A.CLMN,
A.NULLCOUNT,
A.TABLECOUNT,
A.DATATYPE,
(A.NULLCOUNT * 1.0 / A.TABLECOUNT) NULLPERCENT
FROM #TMP A
WHERE A.NULLCOUNT > 0
ORDER BY NULLPERCENT DESC
DROP TABLE #TMP
Thank you, this is great answer, thank you!
I have some columns that actually do not have any value (empty ones, no NULL), so I can modify this and count them in separate query.
Regards,
Brano
May 12, 2009 at 2:52 pm
It was easier to say than to do :), to prepare this query to pull out empty fields. Something with ASCII 32, or similar. Any thoughts?
Brano
May 12, 2009 at 3:05 pm
This query will take a little longer, but it should work.
DECLARE @sql VARCHAR(MAX)
CREATE TABLE #TMP (
CLMN VARCHAR(500),
EMPTYCOUNT INT,
DATATYPE VARCHAR(50),
TABLECOUNT INT)
SELECT @sql = COALESCE(@SQL,'') + CAST('INSERT INTO #TMP Select ''' + TABLE_SCHEMA + '.' + REPLACE(TABLE_NAME,'''','''''') + '.' + COLUMN_NAME + ''' AS Clmn, count(*) EmptyCount, ''' + DATA_TYPE + ''', (Select count(*) FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + ']) AS TableCount FROM ' + TABLE_SCHEMA + '.[' + TABLE_NAME + '] WHERE LTRIM(RTRIM([' + COLUMN_NAME + '])) = '''' ;' + CHAR(13) AS VARCHAR(MAX))
FROM INFORMATION_SCHEMA.COLUMNS
JOIN sysobjects B
ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = B.NAME
WHERE XTYPE = 'U' AND DATA_TYPE 'XML'
EXEC( @sql)
SELECT A.CLMN,
A.EMPTYCOUNT,
A.TABLECOUNT,
A.DATATYPE,
(A.EMPTYCOUNT * 1.0 / A.TABLECOUNT) EMPTYPERCENT
FROM #TMP A
WHERE A.EMPTYCOUNT > 0
ORDER BY EMPTYCOUNT DESC
DROP TABLE #TMP
May 12, 2009 at 3:22 pm
Thanks Ken,
There is one error repeating couple of times:
Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of rtrim function.
Thanks again,
Brano
May 12, 2009 at 3:32 pm
Add this to the where clause.
WHERE XTYPE = 'U' AND DATA_TYPE 'XML' AND DATA_TYPE 'text'
May 13, 2009 at 11:03 am
It works, it works :)!
Thank you Ken, all the best,
Brano
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy