Querying For A List Columns In All Tables

Question

Querying For A List Columns In All Tables

Ronnie Jones

Hall of Fame

Points: 3377
More actions
September 28, 2010 at 10:55 am

#142096

Hello All,
I have an application that uses a back end SQL Server 2005 DB. The application creates 100+ can tables and views during the installatiion. Whenever information is requested, I have to go through each table or view to determine if the requested information is there or at least a column that sounds like it contains the requested data.
I know I can go through sys.tables or sys.views to get a list of all the tables/views in the DB. Is there a way I can query each table for a list of columns? I would want to query all tables/views and save the output in a spreadsheet or CSV file with a header of the table or view name.
Is there a tool or query that anybody knows of that could be used for this?
Thanks in advance for your help!!
Ronnie

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

one of the companion views is sys.columns; so you can join sys.tables (for jsut tables) or sys.objects if you want views and tables:

select

tab.name as TableName,

col.name as ColumnName,

col.column_id as ColumnID

FROM sys.objects tab

LEFT OUTER JOIN sys.columns col

ON tab.object_id = col.object_id

WHERE tab.type IN ('U','V')

ORDER BY tab.name,col.column_id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

WayneS SSC Guru Points: 95460 More actions · Answer 2

This query will produce the table name, and a comma-separated list of columns in that table.

SELECT DISTINCT

t.name,

sq.Columns

FROM sys.tables t

JOIN (

SELECT OBJECT_ID,

Columns = STUFF((SELECT ',' + name

FROM sys.columns sc

WHERE sc.object_id = s.object_id

FOR XML PATH('')),1,1,'')

FROM sys.columns s

) sq ON t.object_id = sq.object_id

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Ronnie Jones Hall of Fame Points: 3377 More actions · Answer 3

Thanks Lowell for the query. It works great. I really appreciate your help!

Ronnie

Ronnie Jones Hall of Fame Points: 3377 More actions · Answer 4

Wayne - Thank you for the query. It works great. I plan to research the "FOR XML PATH" statement just to see the use of it. One way for me to learn.

thanks again!

Ronnie

WayneS SSC Guru Points: 95460 More actions · Answer 5

Ronnie Jones (9/29/2010)
Wayne - Thank you for the query. It works great. I plan to research the "FOR XML PATH" statement just to see the use of it. One way for me to learn.
thanks again!
Ronnie

Well, let me just explain a few things for you that might be hard so find...

1. the column created (',' + name) produces an unnamed column, so there won't be a column token around it.

2. the FOR XML PATH('') specifies not to use a token around the whole string.

3. the stuff function removes the leading comma

I see that I forgot the order by clause; here is corrected code: