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

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

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

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

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

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

你会发现结果如下所示。

谢谢你！

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