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

我有点惊讶居然没人提

sp_help 'mytable'

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

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

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

你可以使用查询:

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

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

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