Simons SQL Blog

select 2 [@cellpadding]

       ,2 [@cellspacing]

       ,1 [@border]

--This returns the header

       ,(select 'Database Table' [text()] for xml path ('th'),type) tr

       ,(select 'Entity Count' [text()] for xml path ('th'),type) tr

       ,(select 'Total Rows' [text()] for xml path ('th'),type) tr

--This returns the rows in the table

       ,(select (select dbtable [text()]  for xml path('td'),type),

                (select entities [text()] for xml path('td'),type),

                (select rows [text()] for xml path('td'),type)

           from (select dbtable  = object_name( object_id ),

                        entities = count( distinct name ),

                        rows     = count( * )

                   from sys.columns

               group by object_name( object_id )) data --name of this alias is irrelevant but you have to have one

        for xml path ('tr'),type) --path('tr') turns each row into a tr element

 for xml path('table'), type

I though you might be able to use the xpath form of the column name i.e. [tr\th] but unfortunately these are merged into one. Although on reading the documentation it refers to merging of next items. So I thought what happens if you stick a NULL value in between columns (NULLs don't appear in XML documents, there non existence implies NULL). Would you believe that this forces the engine to generate a new node.

Some might say that is a bug, well it works for me. We can therefore simplify the query further to the following. This saves a 0.005% in cost :)