SQL query for table analysis

Wed, 17 Jun 2009 06:58:54 Z

Hi,

One of my database tables (SQL Server 2005 Express) has got some 70 to 80 columns in it. After going live, I noted that practically many of these columns often do not receive a value and remain NULL. So now I am planning to split the table into two pieces, one of which would receive only the heavily used columns and the other one having the rarely used ones, and join these two thru a 1-1 relation, thus expecting SQL Server to perform better becuz in most cases it won't have to access the rarely used columns table. I have some 1.5 million records in the table and need to run a query which could tell me the percent usage of each column (means what pecentage of the total rows had non-NULL values in that particular column), something like:

ColumnA - 78.5%
ColumnB - 60.9%
ColumnC - 1.4%

I could have done it using COUNT(ColumnA) with IS NULL, but doing that thing 70 to 80 times seems to be a lot of work. Is there any query that could return the percentage for all columns? Also would it be a good idea to do this splitting anyway? If yes, what are the other recommendations when I do splitting, like indexes, physical files etc.

Thanks
Shujaat

SQL query for table analysis

Wed, 17 Jun 2009 07:57:30 Z

Finally found it. Just in case it would help someone else, here's the way to do it. Replace TotalRowsCount with the result of "SELECT COUNT(*) FROM YourTable".

DECLARE @col varchar(255), @cmd varchar(max)

DECLARE getinfo cursor for

SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID

WHERE t.Name = 'YourTable'

OPEN getinfo

SET @cmd = ''

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0

BEGIN

SET @cmd = @cmd + '(SELECT ''' + @col + ''', CONVERT(varchar,cONVERT(real, (SELECT COUNT(*) FROM YourTable WHERE ([' + @col +

'] IS NULL) OR (LTRIM(RTRIM([' + @col + '])) = ''''))) / TotalRowsCount)) UNION '

FETCH NEXT FROM getinfo into @col

END

SET @cmd = SUBSTRING(@cmd, 1, LEN(@cmd) - 6)

EXEC(@cmd)

CLOSE getinfo

DEALLOCATE getinfo