我需要编写一条SQL Server查询,以获取特定表中列的列表、它们关联的数据类型(及其长度)以及它们是否为非空值。我已经完成了这部分内容。
但是现在我还需要在同一个表中,针对某一列,在该列是主键时返回TRUE
。
我应该如何做到这一点?
我的预期输出是:
Column name | Data type | Length | isnull | Pk
我需要编写一条SQL Server查询,以获取特定表中列的列表、它们关联的数据类型(及其长度)以及它们是否为非空值。我已经完成了这部分内容。
但是现在我还需要在同一个表中,针对某一列,在该列是主键时返回TRUE
。
我应该如何做到这一点?
我的预期输出是:
Column name | Data type | Length | isnull | Pk
为了避免某些列出现重复行,请使用user_type_id而不是system_type_id。
SELECT
c.name 'Column Name',
t.Name 'Data type',
c.max_length 'Max Length',
c.precision ,
c.scale ,
c.is_nullable,
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID('YourTableName')
只需将YourTableName
替换为您实际的表名 - 适用于SQL Server 2005及以上版本。
如果您使用模式,请将YourTableName
替换为YourSchemaName.YourTableName
,其中YourSchemaName
是实际模式名称,YourTableName
是实际表名。
LEFT OUTER JOIN sys.index_columns ic
LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
ON ic.object_id = c.object_id AND ic.column_id = c.column_id AND i.is_primary_key=1
- Razvan Socol存储过程 sp_columns 返回详细的表信息。
exec sp_columns MyTable
MyTable
只是表名,不是模式。若要按模式过滤,请将其作为第二个参数添加:exec sp_columns 'MyTable', 'MySchema'
。 - Daniel您可以使用以下查询:
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, DATETIME_PRECISION,
IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName'
获取除了Pk信息以外的所有所需元数据。
在SQL 2012中,您可以使用:
EXEC sp_describe_first_result_set N'SELECT * FROM [TableName]'
这将为您提供列名以及它们的属性。
试试这个:
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS IC
where TABLE_NAME = 'tablename' and COLUMN_NAME = 'columnname'
SELECT
c.name 'Column Name',
t.name,
t.name +
CASE WHEN t.name IN ('char', 'varchar','nchar','nvarchar') THEN '('+
CASE WHEN c.max_length=-1 THEN 'MAX'
ELSE CONVERT(VARCHAR(4),
CASE WHEN t.name IN ('nchar','nvarchar')
THEN c.max_length/2 ELSE c.max_length END )
END +')'
WHEN t.name IN ('decimal','numeric')
THEN '('+ CONVERT(VARCHAR(4),c.precision)+','
+ CONVERT(VARCHAR(4),c.Scale)+')'
ELSE '' END
as "DDL name",
c.max_length 'Max Length in Bytes',
c.precision ,
c.scale ,
c.is_nullable,
ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
LEFT OUTER JOIN
sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN
sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
c.object_id = OBJECT_ID('YourTableName')
我有点惊讶,没有人提到
sp_help 'mytable'
在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约束的名称。对于这个问题,您可以使用:
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'
SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM information_schema.columns WHERE table_name = '<name_of_table_or_view>'
SELECT *
以查看information_schema.columns返回的信息。SELECT col.TABLE_CATALOG AS [Database]
, col.TABLE_SCHEMA AS Owner
, col.TABLE_NAME AS TableName
, col.COLUMN_NAME AS ColumnName
, col.ORDINAL_POSITION AS OrdinalPosition
, col.COLUMN_DEFAULT AS DefaultSetting
, col.DATA_TYPE AS DataType
, col.CHARACTER_MAXIMUM_LENGTH AS MaxLength
, col.DATETIME_PRECISION AS DatePrecision
, CAST(CASE col.IS_NULLABLE
WHEN 'NO' THEN 0
ELSE 1
END AS bit)AS IsNullable
, COLUMNPROPERTY(OBJECT_ID('[' + col.TABLE_SCHEMA + '].[' + col.TABLE_NAME + ']'), col.COLUMN_NAME, 'IsIdentity')AS IsIdentity
, COLUMNPROPERTY(OBJECT_ID('[' + col.TABLE_SCHEMA + '].[' + col.TABLE_NAME + ']'), col.COLUMN_NAME, 'IsComputed')AS IsComputed
, CAST(ISNULL(pk.is_primary_key, 0)AS bit)AS IsPrimaryKey
FROM INFORMATION_SCHEMA.COLUMNS AS col
LEFT JOIN(SELECT SCHEMA_NAME(o.schema_id)AS TABLE_SCHEMA
, o.name AS TABLE_NAME
, c.name AS COLUMN_NAME
, i.is_primary_key
FROM sys.indexes AS i JOIN sys.index_columns AS ic ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
JOIN sys.objects AS o ON i.object_id = o.object_id
LEFT JOIN sys.columns AS c ON ic.object_id = c.object_id
AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1)AS pk ON col.TABLE_NAME = pk.TABLE_NAME
AND col.TABLE_SCHEMA = pk.TABLE_SCHEMA
AND col.COLUMN_NAME = pk.COLUMN_NAME
WHERE col.TABLE_NAME = 'YourTableName'
AND col.TABLE_SCHEMA = 'dbo'
ORDER BY col.TABLE_NAME, col.ORDINAL_POSITION;