4

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?

3
  • edited with example :) Nov 28, 2012 at 2:40
  • I still don't understand what you are looking for
    – billinkc
    Nov 28, 2012 at 2:41
  • column my bad. that is what i meant then i think Nov 28, 2012 at 3:06

2 Answers 2

9

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.

1
  • Yeah I would do something like this for ease of use, especially since you can do some filtering for names that are close to what you are looking for. Nov 28, 2012 at 2:56
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.