我需要在SQL server上编写一个查询,以获得特定表中的列列表,其相关数据类型(长度)以及它们是否不为空。我已经做到了这么多。
但现在我还需要得到,在同一个表中,对一个列- TRUE,如果该列是一个主键。
我该怎么做呢?
我的期望输出是:
Column name | Data type | Length | isnull | Pk
我需要在SQL server上编写一个查询,以获得特定表中的列列表,其相关数据类型(长度)以及它们是否不为空。我已经做到了这么多。
但现在我还需要得到,在同一个表中,对一个列- TRUE,如果该列是一个主键。
我该怎么做呢?
我的期望输出是:
Column name | Data type | Length | isnull | Pk
当前回答
查找数据类型和长度的组合结果,并以“NULL”和“非空”的形式为空。
SELECT c.name AS 'Column Name',
t.name + '(' + cast(c.max_length as varchar(50)) + ')' As 'DataType',
case
WHEN c.is_nullable = 0 then 'null' else 'not null'
END AS 'Constraint'
FROM sys.columns c
JOIN sys.types t
ON c.user_type_id = t.user_type_id
WHERE c.object_id = Object_id('TableName')
你会发现结果如下所示。
谢谢你!
其他回答
扩展Alex的答案,您可以这样做以获得PK约束
Select C.COLUMN_NAME, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH, C.NUMERIC_PRECISION, C.IS_NULLABLE, TC.CONSTRAINT_NAME
From INFORMATION_SCHEMA.COLUMNS As C
Left Join INFORMATION_SCHEMA.TABLE_CONSTRAINTS As TC
On TC.TABLE_SCHEMA = C.TABLE_SCHEMA
And TC.TABLE_NAME = C.TABLE_NAME
And TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
Where C.TABLE_NAME = 'Table'
我一定是错过了您想要一个标记来确定给定的列是否是PK的一部分,而不是PK约束的名称。你可以用:
Select C.COLUMN_NAME, C.DATA_TYPE, C.CHARACTER_MAXIMUM_LENGTH
, C.NUMERIC_PRECISION, C.NUMERIC_SCALE
, C.IS_NULLABLE
, Case When Z.CONSTRAINT_NAME Is Null Then 0 Else 1 End As IsPartOfPrimaryKey
From INFORMATION_SCHEMA.COLUMNS As C
Outer Apply (
Select CCU.CONSTRAINT_NAME
From INFORMATION_SCHEMA.TABLE_CONSTRAINTS As TC
Join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE As CCU
On CCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
Where TC.TABLE_SCHEMA = C.TABLE_SCHEMA
And TC.TABLE_NAME = C.TABLE_NAME
And TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
And CCU.COLUMN_NAME = C.COLUMN_NAME
) As Z
Where C.TABLE_NAME = 'Table'
存储过程sp_columns返回详细的表信息。
exec sp_columns MyTable
查找数据类型和长度的组合结果,并以“NULL”和“非空”的形式为空。
SELECT c.name AS 'Column Name',
t.name + '(' + cast(c.max_length as varchar(50)) + ')' As 'DataType',
case
WHEN c.is_nullable = 0 then 'null' else 'not null'
END AS 'Constraint'
FROM sys.columns c
JOIN sys.types t
ON c.user_type_id = t.user_type_id
WHERE c.object_id = Object_id('TableName')
你会发现结果如下所示。
谢谢你!
在SQL 2012中,你可以使用:
EXEC sp_describe_first_result_set N'SELECT * FROM [TableName]'
这将为您提供列名及其属性。
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'Table')
BEGIN
SELECT COLS.COLUMN_NAME, COLS.DATA_TYPE, COLS.CHARACTER_MAXIMUM_LENGTH,
(SELECT 'Yes' FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
ON COLS.TABLE_NAME = TC.TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND KCU.TABLE_NAME = TC.TABLE_NAME
AND KCU.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND KCU.COLUMN_NAME = COLS.COLUMN_NAME) AS KeyX
FROM INFORMATION_SCHEMA.COLUMNS COLS WHERE TABLE_NAME = 'Table' ORDER BY KeyX DESC, COLUMN_NAME
END