MSSQLTips

http://www.mssqltips.com/tip.asp?tip=1639

That's a nice script, but it's a little useless if it does not include all the text datatypes.  I have a huge DB with microsoft navision where I'm sure 50% of the columns could use the sparse option (and god knows we could use the space on the san).  But even with 10 000 columns in the DB, I only get 24 rows back from that script (yup Navision uses tons of defaults... but still).

 Also the script doesn't seem to check to see if the column IS NULLABLE, scanning millions of pages for no good reason.  This is definitly not something I'd use in production or in dev during the day.

Don't get me wrong, there's a ot of potential with that script, it's just not used at the moment... and since I don't have any 2008 prod servers yet, I can'T justify using my time to make one that works better.

One more update about Ms Navision, it's over 20 000 columns in the DB (with 2-3 add ons)... only 53 of them are nullable (image datatype).  So sparse seems out of the question at the moment.

Also the way the script is made makes a table scan for every column in every table, this would seem more appropriate :

 INSERT INTO #tmp

UNPIVOT

SELECT

COUNT(CASE WHEN ColName IS NULL THEN 1 ELSE 0 END), 'Schema.ColName'

COUNT(CASE WHEN ColName2 IS NULL THEN 1 ELSE 0 END), 'Schema.ColName'

COUNT(CASE WHEN ColName3 IS NULL THEN 1 ELSE 0 END), 'Schema.ColName'

FROM dbo.TableName

Then go back and run COUNT(*) FROM the base tables only once or even use syspartitions to avoid completly.

It would also be nice to add another where condition in the final select (or even first select) where only tables with more than 10 000 rows or whatever is appropriate are shown.  In my system, the query tells me I should use sparse columns on tables with 1 row... it's kind of hard to justify going through all the process for that little gain.