sqlserver查看表中字段信息

最新推荐文章于 2025-04-08 09:52:16 发布

rootsoul

最新推荐文章于 2025-04-08 09:52:16 发布

阅读量1k

点赞数 1

分类专栏：笔记文章标签： sqlserver 数据库 sql

本文链接：https://blog.youkuaiyun.com/m0_50836836/article/details/128092654

版权

笔记专栏收录该内容

13 篇文章

订阅专栏

该博客内容展示了如何使用SQL查询SQL Server中的表字段信息，包括列名、数据类型、是否可为空、是否为主键、自动增长等详细属性。通过查询syscolumns和systypes等系统表获取元数据，并用CASE语句处理精度和规模信息。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

记录sqlserver查看表中字段信息

SELECT
	C.NAME AS [列名],
	T.NAME AS [类型],
CASE
	WHEN ( isnull( COLUMNPROPERTY ( C.id, C.NAME, 'Scale' ), 0 ) >= 1 ) THEN
	T.NAME + '(' + CONVERT(nvarchar,COLUMNPROPERTY ( C.id, C.NAME, 'PRECISION' )) + ',' + CONVERT(nvarchar,COLUMNPROPERTY ( C.id, C.NAME, 'Scale' )) + ')'
	WHEN ( isnull( COLUMNPROPERTY ( C.id, C.NAME, 'Scale' ), 0 ) <= 0 ) THEN
	T.NAME + '(' + CONVERT(nvarchar,COLUMNPROPERTY ( C.id, C.NAME, 'PRECISION' )) + ')'
	END AS columnType,
	isnull( ETP.VALUE, '' ) AS [注释],
	CONVERT ( bit, C.IsNullable ) AS [是否可为空],
	CONVERT (
		bit,
	CASE
			WHEN EXISTS (
			SELECT
				1
			FROM
				sysobjects
			WHERE
				xtype = 'PK'
				AND parent_obj = C.id
				AND NAME IN ( SELECT NAME FROM sysindexes WHERE indid IN ( SELECT indid FROM sysindexkeys WHERE id = C.id AND colid = C.colid ) )
				) THEN
				1 ELSE 0
			END
			) AS  [是否主键]
     ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) AS [自动增长]
     ,C.Length as [占用字节]
     ,COLUMNPROPERTY(C.id,C.name,'PRECISION') as [长度]
     ,isnull(COLUMNPROPERTY(C.id,C.name,'Scale'),0) as [小数位数]
     ,ISNULL(CM.text,'') as [默认值]
     ,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]
FROM syscolumns C
INNER JOIN systypes T ON C.xusertype = T.xusertype
left JOIN sys.extended_properties ETP   ON  ETP.major_id = c.id AND ETP.minor_id = C.colid AND ETP.name ='MS_Description'
left join syscomments CM on C.cdefault=CM.id
WHERE C.id = object_id('test_field')