We help IT Professionals succeed at work.
Get Started

How to export column descriptions for each column header in an SQL table into excel?

1,371 Views
Last Modified: 2013-12-07
In SQL, I was looking to add some kind of description that I could associate with the column names in a table and also fetch from SQL into excel along with the column names & data. Basically I need both column names and their descriptions along with the data.
My post includes the code using which I can add descriptions to objects like columns. The second part gives a query which displays the column descriptions when run from SQL, but does not return them when run from excel using ADO.

Why is this the case only with description? I mean I am able to export into excel the table names and column names using the query I have pasted in my previous post, then why am I not able to export column descriptions?
-- For adding a description to a column
USE vns_db;
GO
EXEC sys.sp_addextendedproperty
@name = N'description_name',  
@value = N'description_value', 
@level0type = N'SCHEMA', @level0name = dbo,    
@level1type = N'TABLE',  @level1name = risk_parameters,  
@level2type = N'COLUMN', @level2name = branch_client; 
GO
 
 
 
 
 
-- For fetching the column name & description
SELECT 
    [Table Name] = OBJECT_NAME(c.object_id),
    [Column Name] = c.name,
    [Description] = ex.value 
FROM 
    sys.columns c 
LEFT OUTER JOIN 
    sys.extended_properties ex 
ON 
    ex.major_id = c.object_id
    AND ex.minor_id = c.column_id 
WHERE  
    OBJECT_NAME(c.object_id) = 'risk_parameters'  
ORDER 
    BY OBJECT_NAME(c.object_id), c.column_id

Open in new window

Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE