得到SQL数据库中所有表字段及字段中文描述

[sql] 
SELECT  
(case when a.colorder=1 then d.name else '' end) N'表名',  
a.colorder N'字段序号',  
a.name N'字段名',  
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',  
(case when (SELECT count(*)  
FROM sysobjects  
WHERE (name in  
(SELECT name  
FROM sysindexes  
WHERE (id = a.id) AND (indid in  
(SELECT indid  
FROM sysindexkeys  
WHERE (id = a.id) AND (colid in  
(SELECT colid  
FROM syscolumns  
WHERE (id = a.id) AND (name = a.name))))))) AND  
(xtype = 'PK'))>0 then '√' else '' end) N'主键',  
b.name N'类型',  
a.length N'占用字节数',  
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',  
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',  
(case when a.isnullable=1 then '√'else '' end) N'允许空',  
isnull(e.text,'') N'默认值',  
isnull(g.[value],'') AS N'字段说明'  
--into ##tx  
  
  
FROM syscolumns a left join systypes b  
on a.xtype=b.xusertype  
inner join sysobjects d  
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'  
left join syscomments e  
on a.cdefault=e.id  
left join sysproperties g  
on a.id=g.id AND a.colid = g.smallid  
order by object_name(a.id),a.colorder  

[sql] 
select   
    table_name=  
    (  
    case when t_c.column_id=1   
        then t_o.name   
        else ''   
    end  
    ),  
    column_id=t_c.column_id,  
    column_name=t_c.name,  
    type=t.name,  
    max_length=t_c.max_length,  
    precision=isnull(t_c.precision,0),  
    scale=isnull(t_c.scale,0),  
    is_identity=case when t_c.is_identity=1 then '√' else '' end,      
    is_primary=  
    (  
        case when exists  
        (  
            select 1 from sys.indexes i,sys.index_columns ic,sys.objects o  
                where o.type='PK' and o.name=i.name and i.index_id=ic.index_id   
                    and i.object_id=ic.object_id and ic.column_id=t_c.column_id   
                    and o.parent_object_id=t_c.object_id  
        )  
        then '√'  
        else ''  
        end  
    ),  
    is_nullable=case when t_c.is_nullable=1 then '√' else '' end,  
    default_value=isnull(c.definition,''),  
    description=isnull(e.value,''),  
    fk_column_name=isnull(f_c.name,''),  
    fk_table_name=isnull(f_o.name,'')  
from sys.columns t_c  
    inner join sys.objects t_o on t_c.object_id=t_o.object_id          
    left join sys.types t on t.system_type_id=t_c.system_type_id   
        and t.user_type_id=t_c.user_type_id  
    left join sys.default_constraints c on c.object_id=t_c.default_object_id   
        and c.parent_object_id=t_c.object_id and c.parent_column_id=t_c.column_id  
    left join sys.extended_properties e on e.major_id=t_c.object_id   
        and e.minor_id=t_c.column_id      
    left join   
    (  
        select parent_object_id,referenced_object_id,column_id=min(key_index_id) from sys.foreign_keys  
            group by parent_object_id,referenced_object_id  
    )f on f.parent_object_id=t_c.object_id and f.column_id=t_c.column_id    
    left join sys.columns f_c on f_c.object_id=f.referenced_object_id and f_c.column_id=f.column_id  
    left join sys.objects f_o on f_o.object_id=f.referenced_object_id  
where t_o.type='U' and t_o.name<>





'sysdiagrams'  
    order by t_o.name,t_c.column_id