抛出了另一种在SQL server中解决问题的方法。 我的小脚本在这里应该返回列名，数据类型，是空的，约束和索引名称。 您还可以包括任何额外的列，如精度，比例… (你需要用你的数据库名、Schema name和Table name替换数据库名) .返回列的顺序与从'select * from table'中返回列的顺序相同

USE DBA -- Replace Database Name with yours

DECLARE @SCHEMA VARCHAR(MAX)
DECLARE @TABLE_NAME VARCHAR(MAX)
DECLARE @SCHEMA_TABLE_NAME VARCHAR(MAX)

SET @SCHEMA = REPLACE(REPLACE('[SCHEMA NAME]', '[', ''), ']', '')--Replace Schema Name with yours
SET @TABLE_NAME = REPLACE(REPLACE('[TABLE NAME]', '[', ''), ']', '') --' Replace Table  Name with yours
SET @SCHEMA_TABLE_NAME = @SCHEMA + '.' + @TABLE_NAME;


WITH SchemaColumns
AS (
    SELECT C.COLUMN_NAME,
        IS_NULLABLE,
        DATA_TYPE,
        CHARACTER_MAXIMUM_LENGTH,
        C.ORDINAL_POSITION
    FROM INFORMATION_SCHEMA.COLUMNS AS C
    WHERE C.TABLE_SCHEMA = @SCHEMA
        AND C.TABLE_NAME = @TABLE_NAME
    ),
SchemaConstraints
AS (
    SELECT CN.COLUMN_NAME,
        CC.CONSTRAINT_TYPE
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS CC
    INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS CN ON CC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME
    WHERE CC.TABLE_SCHEMA = @SCHEMA
        AND CC.TABLE_NAME = @TABLE_NAME
    ),
SchemaIndex
AS (
    SELECT I.name AS index_name,
        COL_NAME(IC.object_id, IC.column_id) AS column_name,
        IC.index_column_id,
        IC.key_ordinal,
        IC.is_included_column
    FROM sys.indexes AS i
    INNER JOIN sys.index_columns AS IC ON I.object_id = IC.object_id
        AND I.index_id = IC.index_id
    WHERE I.object_id = OBJECT_ID(@SCHEMA_TABLE_NAME)
    )
SELECT ISNULL(SchemaColumns.COLUMN_NAME, '') "Column Name",
    CASE 
        WHEN SchemaColumns.CHARACTER_MAXIMUM_LENGTH IS NULL
            THEN UPPER(ISNULL(SchemaColumns.DATA_TYPE, ''))
        ELSE CONCAT (
                UPPER(ISNULL(SchemaColumns.DATA_TYPE, '')),
                '(',
                CAST(SchemaColumns.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(50)),
                ')'
                )
        END "Data Type",
    SchemaColumns.IS_NULLABLE "Is Nullable",
    ISNULL(SchemaConstraints.CONSTRAINT_TYPE, '-') "Constraints",
    ISNULL(STRING_AGG(CONVERT(NVARCHAR(max), SchemaIndex.INDEX_NAME), CHAR(13)), '-') "Indexes Names"
FROM SchemaColumns
LEFT JOIN SchemaConstraints ON SchemaConstraints.COLUMN_NAME = SchemaColumns.COLUMN_NAME
LEFT JOIN SchemaIndex ON SchemaColumns.COLUMN_NAME = SchemaIndex.COLUMN_NAME
GROUP BY SchemaColumns.COLUMN_NAME,
    SchemaColumns.DATA_TYPE,
    SchemaColumns.CHARACTER_MAXIMUM_LENGTH,
    SchemaColumns.IS_NULLABLE,
    SchemaConstraints.CONSTRAINT_TYPE,
    SchemaColumns.ORDINAL_POSITION
ORDER BY SchemaColumns.ORDINAL_POSITION

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

exec sp_columns MyTable

将另一个答案放入环中，这将给你这些列和更多:

SELECT col.TABLE_CATALOG AS [Database]
     , col.TABLE_SCHEMA AS Owner
     , col.TABLE_NAME AS TableName
     , col.COLUMN_NAME AS ColumnName
     , col.ORDINAL_POSITION AS OrdinalPosition
     , col.COLUMN_DEFAULT AS DefaultSetting
     , col.DATA_TYPE AS DataType
     , col.CHARACTER_MAXIMUM_LENGTH AS MaxLength
     , col.DATETIME_PRECISION AS DatePrecision
     , CAST(CASE col.IS_NULLABLE
                WHEN 'NO' THEN 0
                ELSE 1
            END AS bit)AS IsNullable
     , COLUMNPROPERTY(OBJECT_ID('[' + col.TABLE_SCHEMA + '].[' + col.TABLE_NAME + ']'), col.COLUMN_NAME, 'IsIdentity')AS IsIdentity
     , COLUMNPROPERTY(OBJECT_ID('[' + col.TABLE_SCHEMA + '].[' + col.TABLE_NAME + ']'), col.COLUMN_NAME, 'IsComputed')AS IsComputed
     , CAST(ISNULL(pk.is_primary_key, 0)AS bit)AS IsPrimaryKey
  FROM INFORMATION_SCHEMA.COLUMNS AS col
       LEFT JOIN(SELECT SCHEMA_NAME(o.schema_id)AS TABLE_SCHEMA
                      , o.name AS TABLE_NAME
                      , c.name AS COLUMN_NAME
                      , i.is_primary_key
                   FROM sys.indexes AS i JOIN sys.index_columns AS ic ON i.object_id = ic.object_id
                                                                     AND i.index_id = ic.index_id
                                         JOIN sys.objects AS o ON i.object_id = o.object_id
                                         LEFT JOIN sys.columns AS c ON ic.object_id = c.object_id
                                                                   AND c.column_id = ic.column_id
                  WHERE i.is_primary_key = 1)AS pk ON col.TABLE_NAME = pk.TABLE_NAME
                                                  AND col.TABLE_SCHEMA = pk.TABLE_SCHEMA
                                                  AND col.COLUMN_NAME = pk.COLUMN_NAME
 WHERE col.TABLE_NAME = 'YourTableName'
   AND col.TABLE_SCHEMA = 'dbo'
 ORDER BY col.TABLE_NAME, col.ORDINAL_POSITION;

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

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

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'个表。