sql server - Trying to format SQL query results -

found query here on stack overflow found helpful pull table names , corresponding columns microsoft sql server enterprise edition (64-bit) 10.50.4286 sp2 database.

select   o.name, c.name     sys.columns c  join     sys.objects o on o.object_id = c.object_id     o.type = 'u'  order o.name, c.name 

it produces table 2 columns this, each row has table name in column 01 , corressponding columns in column 02:

what want this, 1 column each table name , tables columns listed below this:

i've started doing manually in excel, on 5000 rows returned nice if there way format results in query this. in advance!

as telling you, un-sql-y thing do. resultset have arbitrary number of columns (equal number of user tables in database, huge). since resultset must rectangular, have many rows maximum number of columns in of tables, many of values null.

that said, straightforward dynamic pivot gets want:

declare @columns nvarchar(max); declare @sql nvarchar(max);  set @columns = stuff ( (                     select '],[' + t.name                     sys.tables t                     t.type = 'u'                     xml path('') ), 1, 2, '')                 + ']';  set @sql = '     select ' + @columns + '          (          select   t.name tname                  , c.name cname                  , row_number() on (partition t.name order c.name) rn             sys.columns c          join     sys.tables t on t.object_id = c.object_id             t.type = ''u''      ) raw     pivot (max(cname) tname in ( ' + @columns + ' ))      pvt;     ';  execute(@sql); 

this produces on master database:

spt_fallback_db     spt_fallback_dev    spt_fallback_usg    spt_monitor     msreplication_options ------------------- ------------------- ------------------- --------------- ---------------------- dbid                high                dbid                connections     install_failures name                low                 lstart              cpu_busy        major_version status              name                segmap              idle            minor_version version             phyname             sizepg              io_busy         optname xdttm_ins           status              vstart              lastrun         revision xdttm_last_ins_upd  xdttm_ins           xdttm_ins           pack_errors     value xfallback_dbid      xdttm_last_ins_upd  xdttm_last_ins_upd  pack_received   null xserver_name        xfallback_drive     xfallback_vstart    pack_sent       null null                xfallback_low       xserver_name        total_errors    null null                xserver_name        null                total_read      null null                null                null                total_write     null  (11 row(s) affected)