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

最新推荐文章于 2023-06-01 14:37:19 发布

翻译最新推荐文章于 2023-06-01 14:37:19 发布 · 1.2k 阅读

文章标签：

#SQL #数据库 #字段 #说明

本文详细解析了SQL2000和SQL2005中用于获取表字段信息的SQL查询语句，包括字段名、序号、类型、长度、标识、主键、允许空、默认值、说明等关键属性。

SQL2000 写法：

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

SQL2005 写法：

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

如果不是很明白,可以将以上代码放到SQL查询分析器上打开一个数据库，运行一下就知道了