DECLARE @col NVARCHAR(MAX);
SELECT @col= COALESCE(@col, '') + ',' + COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS WHERE  Table_name = 'MxLocations';
SELECT @col;

一种方法是查询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

我使用

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%'

虽然@Gulzar Nazim的回答很好，但在查询中包含数据库名称可能更容易，这可以通过下面的SQL实现。

SELECT COLUMN_NAME, *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'you-table-name' AND TABLE_CATALOG='your-database-name'

不确定2008版是否有更简单的方法。

USE [Database Name]
SELECT COLUMN_NAME,* 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName' AND TABLE_SCHEMA='YourSchemaName'

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