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

你会发现结果如下所示。

谢谢你！

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

查询:执行sp_description _first_result_set并选择年收入 [DB_NAME]。[DBO]。(员工)”

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

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

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

为了确保获得正确的长度，需要将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')