finding sparse column in your tables

tonight i was working on compressing indexes for a sharepoint database.  I ran into some issue because some of the columns were using sparse data type, which is great for saving space on null values.  Because of this, you can’t compress the table or indexes.

the following script will help you identify which table has sparse columns instead of looking thru it one at time

select distinct o.name from sys.columns c
right join sys.objects o
on c.object_id = o.object_id
where o.type = ‘U’
and COLUMNPROPERTY( OBJECT_ID(o.name),c.name,’issparse’) = 1