在SqlServer2005环境下查看当前数据库及表、字段等定义信息的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