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?
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.
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
