SQLSERVER查看数据库表的字段类型，是否允许为NULL，默
认值，主键等
declare@table_name varchar(100)-- 表名
set@table_name='bqcform101'
--============表结构
select类别，表名or字段名，描述，字段类型，是否⾃增，允许为NULL，默认值from
(
SELECT'表名'类别,-1 column_id,tbs.name 表名or字段名,ds.value 描述,''字段类型,''是否⾃增,''允许为NULL,''默认值,1 rn
isnull的用法ded_properties ds
LEFT JOIN sysobjects tbs ON ds.major_id=tbs.id
WHERE ds.minor_id=0and tbs.name=@table_name
union
SELECT
@table_name类别
,c.column_id
,C.name 表名or字段名
,s.value 描述
，字段类型= T.name +CASE T.user_type_id WHEN41THEN'('+CAST(C.scale AS VARCHAR) +')'-- time
WHEN42THEN'('+CAST(C.scale AS VARCHAR) +')'-- datetime2
WHEN43THEN'('+CAST(C.scale AS VARCHAR) +')'-- datetimeoffset
WHEN106THEN'('+CAST(C.precision AS VARCHAR)+','+CAST(C.scale AS VARCHAR) +')'-- decimal
WHEN108THEN'('+CAST(C.precision AS VARCHAR)+','+CAST(C.scale AS VARCHAR) +')'-- numeric
WHEN165THEN'('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'-- varbinary
WHEN167THEN'('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'-- varchar
WHEN173THEN'('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'-- binary
WHEN175THEN'('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'-- char
WHEN231THEN'('+ISNULL(CAST(NULLIF(C.max_length,-1)/2AS VARCHAR),'MAX') +')'-- nvarchar(该字段校检根据实际情况)
WHEN239THEN'('+ISNULL(CAST(NULLIF(C.max_length,-1) AS VARCHAR),'MAX') +')'-- nchar
ELSE''
END
,case when C.is_identity=1then'是'else''end是否⾃增
--cast(C.is_identity as varchar(10)) 是否⾃增
,case when C.is_nullable=1then'是'else''end允许为NULL
，默认值=ISNULL(STUFF(LEFT(D.definition,LEN(D.definition)-1),1,1,''),'')
,3 rn
lumns C
INNER pes T ON C.user_type_id = T.user_type_id
LEFT JOIN sys.default_constraints D ON D.[object_id]=C.default_object_id AND D.parent_object_id = C.[object_id]AND D.parent_column_id = C.column_id left join (select major_id,minor_id,value ded_properties) s on s.major_id = c.object_id and s.minor_id = c.column_id
WHERE C.[object_id]=OBJECT_ID(@table_name)
) s
order by column_id,rn
--============主键
select Primary_COLUMN_NAME =convert(sysname,c.name)
from
sysindexes i, syscolumns c, sysobjects o
where o.id =object_id(@table_name)
and o.id = c.id
and o.id = i.id
and (i.status &0x800) =0x800
and (c.name =index_col (@table_name, i.indid, 1) or
c.name =index_col (@table_name, i.indid, 2) or
c.name =index_col (@table_name, i.indid, 3) or
c.name =index_col (@table_name, i.indid, 4) or
c.name =index_col (@table_name, i.indid, 5) or
c.name =index_col (@table_name, i.indid, 6) or
c.name =index_col (@table_name, i.indid, 7) or
c.name =index_col (@table_name, i.indid, 8) or
c.name =index_col (@table_name, i.indid, 9) or
c.name =index_col (@table_name, i.indid, 10) or
c.name =index_col (@table_name, i.indid, 11) or
c.name =index_col (@table_name, i.indid, 12) or
c.name =index_col (@table_name, i.indid, 13) or
c.name =index_col (@table_name, i.indid, 14) or
c.name =index_col (@table_name, i.indid, 15) or
c.name =index_col (@table_name, i.indid, 16)
)
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论