一种方法是查询syscolumns:

select
   syscolumns.name as [Column],
   syscolumns.xusertype as [Type],
   sysobjects.xtype as [Objtype]
from 
   sysobjects 
inner join 
   syscolumns on sysobjects.id = syscolumns.id
where sysobjects.xtype = 'u'
and   sysobjects.name = 'MyTableName'
order by syscolumns.name

set fmtonly on
select * from yourTable

像这样的东西?

sp_columns @table_name=your table name

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'TableName'

我使用

SELECT st.NAME, sc.NAME, sc.system_type_id
FROM sys.tables st
INNER JOIN sys.columns sc ON st.object_id = sc.object_id
WHERE st.name LIKE '%Tablename%'

USE [Database]

SELECT TABLE_NAME,
       TABLE_SCHEMA,
       [Column_Name],
       [Data_type]
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_SCHEMA = 'dbo'