Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Please use the edit button to clarify and improve your question to be more specific. –  Paul White Nov 28 '12 at 2:32
    
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
add comment

2 Answers

up vote 9 down vote accepted

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.

share|improve this answer
    
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. –  hobs Nov 28 '12 at 2:56
add comment
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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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