[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Want to query for column names in a table or Describe (Not T-SQL, just SQL query)

Posted on 2007-10-13
4
Medium Priority
?
4,307 Views
Last Modified: 2009-03-11
I have a million tables to look at.  I want to get the column names without having to retype them.  

DESCRIBE tablename - is giving me an error

SELECT COLUMN_NAME from information_schema WHERE TABLE_NAME = 'tablename' - is giving me an error - Should the information_schema be replaced with the database name or is information_schema a Microsoft provided system schema?

Do I need special privileges?

I'm not using T-SQL, just plain SQL Queries.  Can anybody help me find a way to get the column names in a list from the database?

Thank you in advance,
Patricia

0
Comment
Question by:pdlarue
  • 2
  • 2
4 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 20071645
>DESCRIBE tablename - is giving me an error
is from oracle, hence does not work in MS SQL Server

>SELECT COLUMN_NAME from information_schema WHERE TABLE_NAME = 'tablename' - is giving me an error
you miss the view name:

SELECT COLUMN_NAME from information_schema.columns WHERE TABLE_NAME = 'tablename'

0
 

Author Comment

by:pdlarue
ID: 20071662
angellll:

I got it.  Thank you.  I'll give you the points but can you maybe tell me how to get a data dictionary from
SQL Server?  I have 12 databases and nobody has done any documentation on it at all; not even an ER Diagram.

Thank you,
Patricia
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20071680
the information_schema gives all the technical implementation, but there is no tool to give you a diagram unless there are primary key + foreign key contraints set up...

0
 

Author Comment

by:pdlarue
ID: 20072793
Thank you, angellll, you have been very helpful.  I found the following SQL that gave me a resultset of all tables and their column information.  I just had to query each database.  This might be helpful to you or others in the future.

     SELECT * FROM information_schema.columns

Thanks again,
Patricia
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question