Question

c# 把sql数据库列的属性比如是否为空 是否只读 等等读出来

c#编程 连接sql server数据库后，将给定表的所有列的属性读出来，即读出每列列名、数据类型、默认值、是否允许为空、最大长度、是否只读、是否自增、自增量多少……只要有用价值，就给分。

Answer 1

SELECT
Sysobjects.name AS [表],
syscolumns.name AS [列名],
systypes.name AS [数据类型],
syscolumns.length AS [字段长度],
sys.extended_properties.[value] AS [字段描述],
syscomments.text AS [默认值],
CASE syscolumns.isnullable WHEN '1' THEN 'Y' ELSE 'N' END AS [是否允许空值],
ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'Scale'), 0) AS [小数位数],
COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity') AS [自增标识] ,
CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = syscolumns.id AND colid = syscolumns.colid)))
THEN '√' ELSE '' END AS [主键]
FROM syscolumns
INNER JOIN systypes
ON syscolumns.xtype = systypes.xtype
LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
LEFT OUTER JOIN sys.extended_properties ON
( sys.extended_properties.minor_id = syscolumns.colid
AND sys.extended_properties.major_id = syscolumns.id)
LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
WHERE (systypes.name <> 'sysname')
AND syscolumns.id IN (SELECT id FROM SYSOBJECTS WHERE xtype = 'U' AND NAME = '这里是你要查询的表名')