SELECT column_name, data_type, character_maximum_length, table_name,ordinal_position, is_nullable 
FROM information_schema.COLUMNS WHERE table_name LIKE 'YOUR_TABLE_NAME'
ORDER BY ordinal_position

SELECT name
FROM sys.columns
WHERE object_id = OBJECT_ID('TABLE_NAME')

TABLE_NAME是你的表

它将检查给定的表是否是Base table。

SELECT 
    T.TABLE_NAME AS 'TABLE NAME',
    C.COLUMN_NAME AS 'COLUMN NAME'
FROM INFORMATION_SCHEMA.TABLES T
INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON T.TABLE_NAME=C.TABLE_NAME
    WHERE   T.TABLE_TYPE='BASE TABLE'
            AND T.TABLE_NAME LIKE 'Your Table Name'

简单，不需要sys变量:

SHOW COLUMNS FROM suppliers;

SELECT TOP (0) [toID]
      ,[sourceID]
      ,[name]
      ,[address]
  FROM [ReportDatabase].[Ticket].[To]

简单，不需要任何系统表

您可以使用这个查询

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like N'%[ColumnName]%' and TABLE_NAME = N'[TableName]'