SQL Server 查询以获取表中的列列表以及数据类型、NOT NULL 和 PRIMARY KEY 约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2418527/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL server query to get the list of columns in a table along with Data types, NOT NULL, and PRIMARY KEY constraints
提问by Shrayas
I need to write a query on SQL server to get the list of columns in a particular table, its associated data types (with length) and if they are not null. And I have managed to do this much.
我需要在 SQL 服务器上编写一个查询以获取特定表中的列列表、其关联的数据类型（带长度）以及它们是否不为空。我已经成功做到了这么多。
But now i also need to get, in the same table, against a column - TRUEif that column is a primary key.
但是现在我还需要在同一个表中针对一列获取 -TRUE如果该列是主键。
How do i do this?
我该怎么做呢？
My expected output is:
我的预期输出是：
Column name | Data type | Length | isnull | Pk
回答by marc_s
To avoid duplicate rows for some columns, use user_type_id instead of system_type_id.
为避免某些列出现重复行，请使用 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')
Just replace YourTableNamewith your actual table name - works for SQL Server 2005 and up.
只需替换YourTableName为您的实际表名 - 适用于 SQL Server 2005 及更高版本。
In case you are using schemas, replace YourTableNameby YourSchemaName.YourTableNamewhere YourSchemaNameis the actual schema name and YourTableNameis the actual table name.
如果你正在使用的模式，取代YourTableName由YourSchemaName.YourTableName这里YourSchemaName是实际的架构名称，YourTableName是实际的表名。
回答by decompiled
The stored procedure sp_columns returns detailed table information.
存储过程 sp_columns 返回详细的表信息。
exec sp_columns MyTable
回答by Ajadex
You could use the query:
您可以使用查询：
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, DATETIME_PRECISION,
IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName'
to get all the metadata you require except for the Pk information.
获取您需要的所有元数据，除了 Pk 信息。
回答by Amruta Kar
In SQL 2012 you can use:
在 SQL 2012 中，您可以使用：
EXEC sp_describe_first_result_set N'SELECT * FROM [TableName]'
This will give you the column names along with their properties.
这将为您提供列名称及其属性。
回答by khaleel
Try this:
尝试这个：
select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS IC
where TABLE_NAME = 'tablename' and COLUMN_NAME = 'columnname'
回答by Microsoft Developer
To ensure you obtain the right length you would need to consider unicode types as a special case. See code below.
为了确保您获得正确的长度，您需要将 unicode 类型视为一种特殊情况。请参阅下面的代码。
For further information see: https://msdn.microsoft.com/en-us/library/ms176106.aspx
有关更多信息，请参阅：https: //msdn.microsoft.com/en-us/library/ms176106.aspx
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')
回答by Thomas
Expanding on Alex's answer, you can do this to get the PK constraint
扩展亚历克斯的答案，您可以这样做以获得 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'
I must have missed that you want a flag to determine if the given column was part of the PK instead of the name of the PK constraint. For that you would use:
我一定错过了您想要一个标志来确定给定列是否是 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'
回答by Abu Zafor
wite the table name in the query editor select the name and press Alt+F1 and it will bring all the information of the table.
使用查询编辑器中的表名选择名称并按Alt+F1，它将带来该表的所有信息。
回答by Marquistador
SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM information_schema.columns WHERE table_name = '<name_of_table_or_view>'
Run SELECT *in the above statement to see what information_schema.columns returns.
SELECT *在上面的语句中运行以查看 information_schema.columns 返回什么。
This question has been previously answered - https://stackoverflow.com/a/11268456/6169225
这个问题之前已经回答过 - https://stackoverflow.com/a/11268456/6169225
回答by Mario Levesque
I am a little bit surprised nobody mentioned
我有点惊讶没人提到
sp_help 'mytable'
