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
一只查询SQLServer 2005所有信息的语句
最新推荐文章于 2024-06-30 21:16:43 发布