SQL Server and fetch data from system table

When I am designing table then I am putting text in description attribute of all column.

Suppose column name is empid and column description is Employee ID.

So I 开发者_如何学Goneed to show the data from my table but want to show column name from description attribute which will querying sys table.

How is it possible? Can anyone give me a sample SQL?

You can show the extended property in addition to the values from the table (i.e., as another column), but not in lieu of the column name without using dynamic SQL. If you want to simply return the description with the data, you can do something like this:

Create Table dbo.Test
    (
    EmpId int not null Primary Key
    , Col1 varchar(10) null
    , Col2 varchar(10) null
    )
Insert dbo.Test( EmpId, Col1, Col2 )
Select 1, 'Foo', 'Bar'
Union All Select 2, 'Gamma', 'Theta'
GO
EXEC sys.sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'EmployeeID', 
    @level0type = N'SCHEMA', @level0name = dbo, 
    @level1type = N'TABLE',  @level1name = Test,
    @level2type = N'COLUMN',  @level2name = EmpId;
GO
EXEC sys.sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'Column1', 
    @level0type = N'SCHEMA', @level0name = dbo, 
    @level1type = N'TABLE',  @level1name = Test,
    @level2type = N'COLUMN',  @level2name = Col1;
GO
EXEC sys.sp_addextendedproperty 
    @name = N'MS_Description', 
    @value = N'Column2', 
    @level0type = N'SCHEMA', @level0name = dbo, 
    @level1type = N'TABLE',  @level1name = Test,
    @level2type = N'COLUMN',  @level2name = Col2;
GO
Select T.EmpId, ColDesc.Emp1Desc
    , T.Col1, ColDesc.Col1Desc
    , T.Col2, ColDesc.Col2Desc
From Test As T
    Cross Apply (
                Select Min( Case When objname = 'EmpId' Then value End ) As Emp1Desc
                    , Min( Case When objname = 'Col1' Then value End ) As Col1Desc
                    , Min( Case When objname = 'Col2' Then value End ) As Col2Desc
                From fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'Test', 'column', default)
                ) As ColDesc

If you wanted to just pull the descriptions, you can do that like so:

Select objname, value As Description
From fn_listextendedproperty (NULL, 'schema', 'dbo', 'table', 'Test', 'column', default)

Are you referring to extended properties? If so, you can query sys.extended_properties (see the previous link for more details).