查询数据库所有列

select o.name as table_name,c.name as column_name,t.name as data_type,'' as column_type,c.max_length as character_maximum_length,
s.text as column_default,c.is_nullable,
case
when exists
(
select 1
from sys.objects x
join sys.indexes y on x.Type=N'PK' and x.Name=y.Name
join sysindexkeys z on z.ID=c.Object_id and z.indid=y.index_id and z.Colid=c.Column_id
) then 'PRI'
else null
end as column_key,
cast(is_identity as varchar) as extra,
e.[value] as column_comment
from sys.columns c
inner join sys.objects o on c.object_id=o.object_id
left join sys.types t on c.user_type_id=t.user_type_id
left join syscomments s on c.default_object_id=s.id
left join sys.extended_properties e on e.major_id=o.object_id and e.minor_id=c.Column_id and e.class=1
where o.type='U'
order by table_name,c.column_id;

转载于:https://www.cnblogs.com/zhangrCsharp/p/7906151.html