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

你会发现结果如下所示。

谢谢你！

存储过程sp_columns返回详细的表信息。

exec sp_columns 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是实际的表名。

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

你会发现结果如下所示。

谢谢你！

在SQL 2012中，你可以使用:

EXEC sp_describe_first_result_set N'SELECT * FROM [TableName]'

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

select
      c.name as [column name], 
      t.name as [type name],
      tbl.name as [table name]
from sys.columns c
         inner join sys.types t 
      on c.system_type_id = t.system_type_id 
         inner join sys.tables tbl
      on c.object_id = tbl.object_id
where
      c.object_id = OBJECT_ID('YourTableName1') 
          and 
      t.name like '%YourSearchDataType%'
union
(select
      c.name as [column name], 
      t.name as [type name],
      tbl.name as [table name]
from sys.columns c
         inner join sys.types t 
      on c.system_type_id = t.system_type_id 
         inner join sys.tables tbl
      on c.object_id = tbl.object_id
where
      c.object_id = OBJECT_ID('YourTableName2') 
          and 
      t.name like '%YourSearchDataType%')
union
(select
      c.name as [column name], 
      t.name as [type name],
      tbl.name as [table name]
from sys.columns c
         inner join sys.types t 
      on c.system_type_id = t.system_type_id 
         inner join sys.tables tbl
      on c.object_id = tbl.object_id
where
      c.object_id = OBJECT_ID('YourTableName3') 
          and 
      t.name like '%YourSearchDataType%')
order by tbl.name

根据一个数据库中三个不同表的搜索数据类型，搜索哪个列在哪个表中。这个查询可以扩展到'n'个表。