SQL服务器查询，以获取表中的列列表以及数据类型、NOT NULL和PRIMARY KEY约束

为了避免某些列的重复行，请使用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是实际的表名。

你可以使用查询:

select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, DATETIME_PRECISION,
IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='TableName'

以获得除Pk信息之外的所有元数据。

存储过程sp_columns返回详细的表信息。

exec sp_columns 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的一部分，而不是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, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS IC
where TABLE_NAME = 'tablename' and COLUMN_NAME = 'columnname'

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

select
c.name as [column name],
t.name as [type name],
tbl.name as [table name]
from sys.columns c
inner join sys.types t
on c.system_type_id = t.system_type_id
inner join sys.tables tbl
on c.object_id = tbl.object_id
where
c.object_id = OBJECT_ID('YourTableName1')
and
t.name like '%YourSearchDataType%'
union
(select
c.name as [column name],
t.name as [type name],
tbl.name as [table name]
from sys.columns c
inner join sys.types t
on c.system_type_id = t.system_type_id
inner join sys.tables tbl
on c.object_id = tbl.object_id
where
c.object_id = OBJECT_ID('YourTableName2')
and
t.name like '%YourSearchDataType%')
union
(select
c.name as [column name],
t.name as [type name],
tbl.name as [table name]
from sys.columns c
inner join sys.types t
on c.system_type_id = t.system_type_id
inner join sys.tables tbl
on c.object_id = tbl.object_id
where
c.object_id = OBJECT_ID('YourTableName3')
and
t.name like '%YourSearchDataType%')
order by tbl.name

在一个数据库中，根据您的搜索数据类型为三个不同的表搜索哪个列在哪个表中。这个查询可以扩展到'n'个表。 < / >强

将另一个答案放入环中，这将给你这些列和更多:

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;

在查询编辑器中选择表名并按Alt+F1，它将带来表的所有信息。

为了确保获得正确的长度，需要将unicode类型视为一种特殊情况。参见下面的代码。

有关更多信息，请参阅: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')

SELECT
T.NAME AS [TABLE NAME]
,C.NAME AS [COLUMN NAME]
,P.NAME AS [DATA TYPE]
,P.MAX_LENGTH AS [Max_SIZE]
,C.[max_length] AS [ActualSizeUsed]
,CAST(P.PRECISION AS VARCHAR) +'/'+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM SYS.OBJECTS AS T
JOIN SYS.COLUMNS AS C
ON T.OBJECT_ID = C.OBJECT_ID
JOIN SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID = P.SYSTEM_TYPE_ID
AND C.[user_type_id] = P.[user_type_id]
WHERE T.TYPE_DESC='USER_TABLE'
AND T.name = 'InventoryStatus'
ORDER BY 2

在SQL 2012中，你可以使用:

EXEC sp_describe_first_result_set N'SELECT * FROM [TableName]'

这将为您提供列名及其属性。

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. schema。列的回报。

这个问题之前已经回答过了——https://stackoverflow.com/a/11268456/6169225

查找数据类型和长度的组合结果，并以“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')

你会发现结果如下所示。

谢谢你！

我刚刚做了marc_s“presentation ready”:

SELECT
c.name 'Column Name',
t.name 'Data type',
IIF(t.name = 'nvarchar', c.max_length / 2, c.max_length) 'Max Length',
c.precision 'Precision',
c.scale 'Scale',
IIF(c.is_nullable = 0, 'No', 'Yes') 'Nullable',
IIF(ISNULL(i.is_primary_key, 0) = 0, 'No', 'Yes') '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'

查询: EXEC sp_description _first_result_set N 'SELECT年收入FROM [DB_NAME]。[DBO]。(员工)的< / p >

注意:在某些ide中选择n之前是工作的，或者在某些ide中没有选择n是工作的

这里没有主键，但这可以帮助其他只想拥有一个包含字段名和基本字段属性的表名的用户

USE [**YourDB**]
GO
SELECT tbl.name, fld.[Column Name],fld.[Constraint],fld.DataType
FROM sys.all_objects as tbl left join
(SELECT c.OBJECT_ID,  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
) as fld on tbl.OBJECT_ID = fld.OBJECT_ID
WHERE ( tbl.[type]='U' and tbl.[is_ms_shipped] = 0)
ORDER BY tbl.[name],fld.[Column Name]
GO

Marc_s的答案很好，但如果主键列出现在其他索引中，这些列会出现不止一次，那么它就有一个缺陷。如。

演示:

create table dbo.DummyTable
(
id int not null identity(0,1) primary key,
Msg varchar(80) null
);


create index NC_DummyTable_id ON DummyTable(id);

下面是我用来解决这个问题的存储过程:

create or alter procedure dbo.GetTableColumns
(
@schemaname nvarchar(128),
@tablename nvarchar(128)
)
AS
BEGIN
SET NOCOUNT ON;


with ctePKCols as
(
select
i.object_id,
ic.column_id
from
sys.indexes i
join sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
where
i.is_primary_key = 1
)
SELECT
c.name AS column_name,
t.name AS typename,
c.max_length AS MaxLength,
c.precision,
c.scale,
c.is_nullable,
is_primary_key = CASE WHEN ct.column_id IS NOT NULL THEN 1 ELSE 0 END
FROM
sys.columns c
JOIN sys.types t ON t.user_type_id = c.user_type_id
LEFT JOIN ctePKCols ct ON ct.column_id = c.column_id AND ct.object_id = c.object_id
WHERE
c.object_ID = OBJECT_ID(quotename(@schemaname) + '.' + quotename(@tablename))
    

END
GO


exec dbo.GetTableColumns 'dbo', 'DummyTable'

USE DBA -- Replace Database Name with yours


DECLARE @SCHEMA VARCHAR(MAX)
DECLARE @TABLE_NAME VARCHAR(MAX)
DECLARE @SCHEMA_TABLE_NAME VARCHAR(MAX)


SET @SCHEMA = REPLACE(REPLACE('[SCHEMA NAME]', '[', ''), ']', '')--Replace Schema Name with yours
SET @TABLE_NAME = REPLACE(REPLACE('[TABLE NAME]', '[', ''), ']', '') --' Replace Table  Name with yours
SET @SCHEMA_TABLE_NAME = @SCHEMA + '.' + @TABLE_NAME;




WITH SchemaColumns
AS (
SELECT C.COLUMN_NAME,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
C.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.COLUMNS AS C
WHERE C.TABLE_SCHEMA = @SCHEMA
AND C.TABLE_NAME = @TABLE_NAME
),
SchemaConstraints
AS (
SELECT CN.COLUMN_NAME,
CC.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS CC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CN ON CC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
WHERE CC.TABLE_SCHEMA = @SCHEMA
AND CC.TABLE_NAME = @TABLE_NAME
),
SchemaIndex
AS (
SELECT I.name AS index_name,
COL_NAME(IC.object_id, IC.column_id) AS column_name,
IC.index_column_id,
IC.key_ordinal,
IC.is_included_column
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS IC ON I.object_id = IC.object_id
AND I.index_id = IC.index_id
WHERE I.object_id = OBJECT_ID(@SCHEMA_TABLE_NAME)
)
SELECT ISNULL(SchemaColumns.COLUMN_NAME, '') "Column Name",
CASE
WHEN SchemaColumns.CHARACTER_MAXIMUM_LENGTH IS NULL
THEN UPPER(ISNULL(SchemaColumns.DATA_TYPE, ''))
ELSE CONCAT (
UPPER(ISNULL(SchemaColumns.DATA_TYPE, '')),
'(',
CAST(SchemaColumns.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)),
')'
)
END "Data Type",
SchemaColumns.IS_NULLABLE "Is Nullable",
ISNULL(SchemaConstraints.CONSTRAINT_TYPE, '-') "Constraints",
ISNULL(STRING_AGG(CONVERT(NVARCHAR(max), SchemaIndex.INDEX_NAME), CHAR(13)), '-') "Indexes Names"
FROM SchemaColumns
LEFT JOIN SchemaConstraints ON SchemaConstraints.COLUMN_NAME = SchemaColumns.COLUMN_NAME
LEFT JOIN SchemaIndex ON SchemaColumns.COLUMN_NAME = SchemaIndex.COLUMN_NAME
GROUP BY SchemaColumns.COLUMN_NAME,
SchemaColumns.DATA_TYPE,
SchemaColumns.CHARACTER_MAXIMUM_LENGTH,
SchemaColumns.IS_NULLABLE,
SchemaConstraints.CONSTRAINT_TYPE,
SchemaColumns.ORDINAL_POSITION
ORDER BY SchemaColumns.ORDINAL_POSITION