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?

  • edited with example :) – Julia Roberts Nov 28 '12 at 2:40
  • I still don't understand what you are looking for – billinkc Nov 28 '12 at 2:41
  • column my bad. that is what i meant then i think – Julia Roberts Nov 28 '12 at 3:06
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.

  • 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. – ConstantineK Nov 28 '12 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, privacy policy and cookie policy

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