生成view的描述字段列表

declare @table nvarchar(50);
declare @field_list nvarchar(max);
set @table = N'vwMaterial';
set @field_list = N'';


SELECT      u.name + '.' + t.name AS [table],
            cast(td.value as nvarchar(100)) AS [table_desc],
            cast(c.name as nvarchar(100))  AS [column],
            cast(cd.value as nvarchar(100))  AS [column_desc],
            c.colorder
            into #temp
FROM        sysobjects t
INNER JOIN  sysusers u
    ON      u.uid = t.uid
LEFT OUTER JOIN sys.extended_properties td
    ON      td.major_id = t.id
    AND     td.minor_id = 0
    AND     td.name = 'MS_Description'
INNER JOIN  syscolumns c
    ON      c.id = t.id
LEFT OUTER JOIN sys.extended_properties cd
    ON      cd.major_id = c.id
    AND     cd.minor_id = c.colid
    AND     cd.name = 'MS_Description'
WHERE t.name = @table
ORDER BY  [table],colorder;

select @field_list = @field_list + ',[' + cast([column] as nvarchar(100)) + '] as [' + cast(column_desc as nvarchar(100))  + ']' + NCHAR(10) from 
#temp;

set @field_list = 'select '  + SUBSTRING(@field_list ,2 ,LEN(@field_list)-1) + ' from dbo.' + @table;
select @field_list;

drop table #temp;

 

转载于:https://www.cnblogs.com/coolyylu/p/8136714.html