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'

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

试试这个:

select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE 
from INFORMATION_SCHEMA.COLUMNS IC
where TABLE_NAME = 'tablename' and COLUMN_NAME = 'columnname'

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'

扩展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'

在SQL 2012中，你可以使用:

EXEC sp_describe_first_result_set N'SELECT * FROM [TableName]'

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