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

我刚刚做了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')

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

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

在SQL 2012中，你可以使用:

EXEC sp_describe_first_result_set N'SELECT * FROM [TableName]'

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

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

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'