Display all tables and their columns

Question

Is there a way to display the layouts for all the tables to see their columns? I am trying to find tables with a certain column names.

I am using the web app Web Data Administrator

Example:

table1
- column 1
- column 2
table2
- column 1

is there a way to get a display like that?

Answer 1

Here is a start, if I can guess that by row you actually mean column:

SELECT 
  [table]  = QUOTENAME(OBJECT_SCHEMA_NAME([t.object_id])) +'.'+ QUOTENAME(t.name),
  [column] = c.name
FROM sys.tables AS t
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
ORDER BY t.name, c.column_id;

This doesn't provide exactly the output you want, but surely your presentation tier can - while it is looping through all of the rows - figure out to only list a table when it comes across a new one.

Answer 2

select case when c_n is not null then '-' else t_n end, c_n 
from (
select t_n,c_n, ROW_NUMBER() over(order by t_n,c_n) rn 
from (
select TABLE_NAME t_n,NULL c_n from INFORMATION_SCHEMA.TABLES 
union 
select table_name,column_name from INFORMATION_SCHEMA.columns
) X
) X
order by rn