Use a Query to Access Column Description in SQL

Use a Query to access column description in SQL

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

Change via SQL Query the description of a column

It is not possible to set or change the description of a field using SQL commands.

An MS Access database contains pure database elements like tables and their columns (fields). Both have pure database-related properties like a data type, a field size, a required property, a default value setting or a validation rule. It should be possible to manage all these things using SQL.

But…

This is only a very small part of what an MS Access database can contain. Most of the objects in an Access database are built around these pure database things to display the data in the tables in a user-friendly user interface and let the user/developer create a custom front-end to either show and manage the data in a standard data sheet (which is a special kind of pre-defined Access form) or in more complex and user-defined forms and reports.

To support users in designing a custom user interface around the data, Microsoft has not only defined new object types like forms and reports but also added several properties to the objects on the data layer that are useful when displaying or editing the data in controls like TextBox, ComboBox etc. Some of these properties are Input Mask, Format or Description. They have nothing to do with the storage of the data but instead serve for something visual. These properties can’t be managed using SQL but can be defined and changed programmatically using a library like DAO where each field has a properties collection that does not only contain the original properties from the data layer but also the additional properties that were added to support the “visual” part of Microsoft Access.

Added:

An example in VBA could look something like this, it shouldn't be too difficult to translate it into C#:

Sub SetDescription(TableName As String, FieldName As String, Description As String)

    With CurrentDb
        With .TableDefs(TableName)
            With .Fields(FieldName)
                On Error GoTo Err_SetDescription
                .Properties("Description").Value = Description
            End With
        End With
    End With

Exit_SetDescription:
    Exit Sub

Err_SetDescription:
    Select Case Err.Number
        Case 3270
            With CurrentDb
                With .TableDefs(TableName)
                    With .Fields(FieldName)
                        .Properties.Append .CreateProperty("Description", dbText, Description)
                        Resume Next
                    End With
                End With
            End With
        Case Else
            MsgBox Err.Description, vbExclamation
            Resume Exit_SetDescription
    End Select

End Sub

adding a column description

I'd say you will probably want to do it using the sp_addextendedproperty stored proc.

Microsoft has some good documentation on it.

Try this:

EXEC sp_addextendedproperty 
    @name = N'MS_Description', @value = 'Hey, here is my description!',
    @level0type = N'Schema',   @level0name = 'yourschema',
    @level1type = N'Table',    @level1name = 'YourTable',
    @level2type = N'Column',   @level2name = 'yourColumn';
GO

Any way to retrieve SQL Field Description property with a datarows query?

You can get those descriptions back via query for the whole table.

This is almost entirely lifted from Phil Factor on SQL Server Central. My modifications are only the extra join condition p.name = 'MS_Description' and the where clause.

SELECT SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
       tbl.name AS [Table_Name],
       clmns.name AS [Column_Name],
       p.name AS [Name],
       CAST(p.value AS SQL_VARIANT) AS [Value]
  FROM sys.tables AS tbl
       INNER JOIN sys.all_columns AS clmns ON clmns.OBJECT_ID=tbl.OBJECT_ID
       INNER JOIN sys.extended_properties AS p ON p.major_id=clmns.OBJECT_ID
                                              AND p.minor_id=clmns.column_id
                                              AND p.class= 1
                                              AND p.name = 'MS_Description'
 WHERE tbl.name = 'Your Table Name'                                       
ORDER BY [Table_Schema] ASC,
         [Table_Name] ASC,
         [Column_ID] ASC,
         [Name] ASC

Is it possible to add description for column while creating table in MSSQL QUERY

It's clunky in SQL Server. For some reason, they've never adopted the COMMENT syntax, and you can't add the comments directly in the CREATE TABLE statement.

After your CREATE statement, run the system stored procedure sp-addextendedproperty

There's an extended conversation on the topic under this question: SQL Comments on Create Table on SQL Server 2008

Getting View's Column Description

Finally i got the answer.

SELECT VIEW_COLUMN_NAME=c.name,VIEW_CATALOG,VIEW_SCHEMA,VIEW_NAME,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME, ep.value as 'COLUMN_DESCRIPTION'
FROM sys.columns c
INNER JOIN sys.views vw on c.OBJECT_ID = vw.OBJECT_ID
INNER JOIN sys.schemas s ON s.schema_id = vw.schema_id
LEFT JOIN INFORMATION_SCHEMA.VIEW_COLUMN_USAGE vcu on vw.name = vcu.VIEW_NAME and s.name = vcu.VIEW_SCHEMA and c.name = vcu.COLUMN_NAME
LEFT JOIN (
    SELECT distinct SCM_Name=SCM.Name,TBL_Name=TBL.name,COLName=COL.name,COL_Object_id= COL.object_id,COL_column_id=COL.column_id
    FROM
    SYS.COLUMNS COL
    INNER JOIN SYS.TABLES TBL on COL.object_id = TBL.object_id
    INNER JOIN SYS.SCHEMAS SCM ON TBL.schema_id = SCM.schema_id) tempTBL on tempTBL.TBL_Name=vcu.TABLE_NAME and tempTBL.SCM_Name=TABLE_SCHEMA and tempTBL.COLName = vcu.COLUMN_NAME
Left join sys.extended_properties ep on tempTBL.COL_Object_id = ep.major_id and tempTBL.COL_column_id = ep.minor_id
where vw.NAME = 'v_ProductInfo'

SQL Server manage column descriptions by script

Combined with Steph Locke's suggestion, you can check for existence of the extended proc using:

if exists(
    SELECT  * 
    FROM    sys.extended_properties p
            join sys.columns c on p.major_id = c.object_id and p.minor_id = c.column_id 
    where   p.major_id = OBJECT_ID('yourtablename','table')
            and p.name = 'Description'  
)

How can I get column names from a table in SQL Server?

You can obtain this information and much, much more by querying the Information Schema views.

This sample query:

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'Customers'

Can be made over all these DB objects:

• CHECK_CONSTRAINTS
• COLUMN_DOMAIN_USAGE
• COLUMN_PRIVILEGES
• COLUMNS
• CONSTRAINT_COLUMN_USAGE
• CONSTRAINT_TABLE_USAGE
• DOMAIN_CONSTRAINTS
• DOMAINS
• KEY_COLUMN_USAGE
• PARAMETERS
• REFERENTIAL_CONSTRAINTS
• ROUTINES
• ROUTINE_COLUMNS
• SCHEMATA
• TABLE_CONSTRAINTS
• TABLE_PRIVILEGES
• TABLES
• VIEW_COLUMN_USAGE
• VIEW_TABLE_USAGE
• VIEWS

---

Related Topics