sql获取表的所有字段及属性
时间：2021-07-01 10:21:17
帮助过：137人阅读
select
2 c.name
as name,t.name
as type
3 ,
convert(
bit,c.IsNullable)
as isNULL
4 ,
convert(
bit,
case when exists(
select 1 from sysobjects
where xtype
=‘PK‘ and parent_obj
=c.id
and name
in (
5 select name
from sysindexes
where indid
in(
6 select indid
from sysindexkeys
where id
= c.id
and colid
=c.colid)))
then 1 else 0 end)
7 as isKey
8 ,
convert(
bit,
COLUMNPROPERTY(c.id,c.name,
‘IsIdentity‘))
as isIdentity
9
10 ,
COLUMNPROPERTY(c.id,c.name,
‘PRECISION‘)
as length
11 ,
ISNULL(CM.
text,
‘‘)
as defualtVal
12 ,
isnull(ETP.value,
‘‘)
AS describe
13 --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]
14 from syscolumns c
15 inner join systypes t
on c.xusertype
= t.xusertype
16 left join sys.extended_properties ETP
on ETP.major_id
= c.id
and ETP.minor_id
= c.colid
and ETP.name
=‘MS_Description‘
17 left join syscomments CM
on c.cdefault
=CM.id
18 where c.id
= object_id(
‘表名‘)
附上获取数据库内所有用户新增的表的语句
select * from sys.all_objects where type=‘U‘
sql获取表的所有字段及属性
标签：sel exe null style number 数据 describe type length
