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

Posted on 2007-10-13
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,

Question by:pdlarue
    LVL 142

    Accepted Solution

    >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'


    Author Comment


    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,
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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...


    Author Comment

    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,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now