Use a Query to access column description in SQL

Question

I am trying to access the Column description properties using the INFORMATION_SCHEMA

I have created this Query in the past to get the column name but i can not figure out how to get description of the column

SELECT COLUMN_NAME AS Output, ORDINAL_POSITION 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE  (TABLE_NAME = @Tablename) AND (ORDINAL_POSITION = @Location)

Screen shot

This is where the Description is on the field properties

for the record this question is the mirror of https://stackoverflow.com/questions/3754180/adding-a-column-description — Tim Abell, Jun 19 '19 at 07:30
Here's my sql script for full bi-directional read/write of these ms_description entries: https://gist.github.com/timabell/6fbd85431925b5724d2f - feel free to take what you want from it or use the whole thing. — Tim Abell, Jun 19 '19 at 07:31

score 87 · Accepted Answer · answered Mar 01 '13 at 16:38

87

If by 'description' you mean 'Description' displayed in SQL Management Studio in design mode, here it is:

    select 
        st.name [Table],
        sc.name [Column],
        sep.value [Description]
    from sys.tables st
    inner join sys.columns sc on st.object_id = sc.object_id
    left join sys.extended_properties sep on st.object_id = sep.major_id
                                         and sc.column_id = sep.minor_id
                                         and sep.name = 'MS_Description'
    where st.name = @TableName
    and sc.name = @ColumnName

answered Mar 01 '13 at 16:38

Incidently

4,249
3
23
30

What would the best way for me to link the ORDINAL_POSITION to this? ( is it best to use it directly in the query or should i use the COLUMN_NAME found in the query example i first posted to find the description?) Thank you very much – Will Peckham Mar 02 '13 at 17:23
1

I believe INFORMATION_SCHEMA.COLUMNS.ORDINAL_POSITION is the same as sys.columns.column_id, so you should be able to use it directly when querying sys.extended_properties (as minor_id) – Incidently Mar 02 '13 at 23:13
I am constantly having this VARANT type not supported error. :( and connection got dropped right after it. mssql2012 – Ben Jan 05 '18 at 12:30

score 16 · Answer 2 · answered Jun 07 '17 at 10:19

If you specifically want to use INFORMATION_SCHEMA (as I was) then the following query should help you obtain the column's description field:

SELECT COLUMN_NAME AS [Output]
    ,ORDINAL_POSITION
    ,prop.value AS [COLUMN_DESCRIPTION]
FROM INFORMATION_SCHEMA.TABLES AS tbl
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS col ON col.TABLE_NAME = tbl.TABLE_NAME
INNER JOIN sys.columns AS sc ON sc.object_id = object_id(tbl.table_schema + '.' + tbl.table_name)
    AND sc.NAME = col.COLUMN_NAME
LEFT JOIN sys.extended_properties prop ON prop.major_id = sc.object_id
    AND prop.minor_id = sc.column_id
    AND prop.NAME = 'MS_Description'
WHERE tbl.TABLE_NAME = @TableName

thanks, just what I needed. Not sure why that would be downvoted. — Rob, Jan 22 '18 at 11:34

Dennis T --Reinstate Monica-- · Answer 3 · 2018-07-30T17:33:54.593

The fn_listextendedproperty system function will do what you're looking for.

It’s also referred to as sys.fn_listextendedproperty.

Syntax is as follows:

fn_listextendedproperty ( 
  { default | 'property_name' | NULL } 
, { default | 'level0_object_type' | NULL } 
, { default | 'level0_object_name' | NULL } 
, { default | 'level1_object_type' | NULL } 
, { default | 'level1_object_name' | NULL } 
, { default | 'level2_object_type' | NULL } 
, { default | 'level2_object_name' | NULL } 
)

Example Usage: Lists extended properties for all columns of the ScrapReason table in the Production schema

USE AdventureWorks2012;
GO
SELECT objtype, objname, name, value
FROM fn_listextendedproperty (NULL, 'schema', 'Production', 'table', 'ScrapReason', 'column', NULL);
GO

sp_helptext will not work since it can't be used for tables as per TechNet.

Displays the definition of a user-defined rule, default, unencrypted Transact-SQL stored procedure, user-defined Transact-SQL function, trigger, computed column, CHECK constraint, view, or system object such as a system stored procedure.

sp_columns does not return the sys.extended_properties.value field which you're looking for.

fn_listextendedproperty is arguably easier to work with and more generic than the query in the accepted answer.

score 2 · Answer 4 · edited May 23 '17 at 12:17

2

exec sp_columns @Tablename... That's the system stored procedure that will give you the info.

Other than that, here is a post with a lot of good information on the INFORMATION SCHEMA views: What is the equivalent of 'describe table' in SQL Server?

edited May 23 '17 at 12:17

Community

1
1

answered Mar 01 '13 at 16:16

Chains

12,541
8
45
62

score 2 · Answer 5 · answered Apr 23 '22 at 20:34

If you want to have table with table schema, table name, column name and description you can use following query:


select TableName = tbl.table_schema + '.' + tbl.table_name, 
       sc.name [Column],
       sep.value [Description]
       
from sys.tables st
inner join information_schema.tables tbl on st.object_id=object_id(tbl.table_schema + '.' + tbl.table_name) 
inner join sys.columns sc on st.object_id = sc.object_id
left join sys.extended_properties sep on st.object_id = sep.major_id
                                         and sc.column_id = sep.minor_id
                                         and sep.name = 'MS_Description'

This will generate follwing table:

Table name	Column name	Description
dbo.VersionInfo	AppliedOn	Description

I wanted to generate the md file in my CI/CD pipeline and for this purpose I created a PowerShell module. It has Out-Md switch which generates MD file with all descriptions.

Get-ColumnsDescription -Verbose -ServerInstance ".\SQL2019" -Database PTMeetings -OutMD

To cover topic completely: If you would like to add the description with the query you can use

EXEC sys.sp_addextendedproperty
@name=N'MS_Description' --name of property
,@value=N'Age should be between 12 and 100 Descp' --descption text
,@level0type=N'SCHEMA'
,@level0name=N'jl' --Schema name
,@level1type=N'TABLE'
,@level1name=N'JournalItemNotes' --Table Name
,@level2type=N'COLUMN'
,@level2name=N'NotesType'   -- Column Name

And if You are interested in the idempotent function I propose to create procedure:

IF OBJECT_ID ('dbo.usp_addorupdatedescription', 'P') IS NOT NULL
    DROP PROCEDURE dbo.usp_addorupdatedescription;
GO

CREATE PROCEDURE usp_addorupdatedescription
    @table nvarchar(128),  -- table name
    @column nvarchar(128), -- column name, NULL if description for table
    @descr sql_variant     -- description text
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @c nvarchar(128) = NULL;

    IF @column IS NOT NULL
        SET @c = N'COLUMN';

    BEGIN TRY
        EXECUTE sp_updateextendedproperty  N'MS_Description', @descr, N'SCHEMA', N'dbo', N'TABLE', @table, @c, @column;
    END TRY
    BEGIN CATCH
        EXECUTE sp_addextendedproperty N'MS_Description', @descr, N'SCHEMA', N'dbo', N'TABLE', @table, @c, @column;
    END CATCH;
END
GO

score 0 · Answer 6 · edited May 23 '17 at 12:09

0

Are you looking for the information in the sys.extended_properties view?

https://stackoverflow.com/a/15008885/1948904

edited May 23 '17 at 12:09

Community

1
1

answered Mar 01 '13 at 16:37

Drew Leffelman

516
2
8

Use a Query to access column description in SQL

6 Answers6

Linked