How use "Description" as a column name

_{Saturday, March 15, 2014 10:11 PM}

hello I want use "Description property" as a column name.

like

Select * as [their Description property] from myTabI 

I Find this code for getting "Description property"  of each column

   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'

But I don't konw how can I use it as a dynamic name  for a column

thanks for your time

_{Sunday, March 16, 2014 5:23 AM ✅Answered | 1 vote}

Max limit for identifier/column name is 128 character. What if the description is more than 128 character ?

However if the identifier or description is less than 129 then we can do it using dynamic query. Refer below,

declare @tablename varchar(100)='myTable'declare @sqlstring varchar(max);with cte as ( 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'), cte1 as (   select [Table],[Column],case    when [Description] is null then [Column]    else left(cast([Description] as varchar(200)),128) end as [Description]     from cte)select @sqlstring= Stuff((select ',[' + [Column] + '] as [' + [Description] + ']'              from   cte1 where [Table] = @tablename              for xml path('')), 1, 1, '')set @sqlstring = 'select ' + @sqlstring + ' from ' + @tablenameexec (@sqlstring)

Regards, RSingh

_{Saturday, March 15, 2014 11:20 PM}

But I don't konw how can I use it as a dynamic name  for a column

I think you want to use the extended property description as a column header name, not necessarily as the column name within the query itself.  You might be able to leverage extended properties for this purpose within application code but there is nothing in T-SQL that will do that for you automatically.  But consider that not all columns have extended properties and nothing will enforce the description to be unique.

Dan Guzman, SQL Server MVP, http://www.dbdelta.com