获取除主键之外的列

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
GO
CREATE TABLE t(
	id INT IDENTITY(1,1) PRIMARY KEY,
	c1 INT,
	c2 int	
)
GO
DECLARE @sql NVARCHAR(MAX)

SELECT @sql=STUFF(
	(
		SELECT ','+c.name 
		FROM sys.[columns] AS c WHERE c.[object_id]=OBJECT_ID('t') AND NOT EXISTS(
			SELECT * FROM sys.indexes AS i INNER JOIN sys.index_columns AS ic ON i.[object_id]=ic.[object_id] AND i.[object_id]=c.[object_id] 
			AND i.is_primary_key=1 AND ic.column_id=c.column_id	
	    )
	    FOR XML PATH('')
	),1,1,'' 
)
SET @sql='select '+@sql+' from t'
SELECT @sql
/*
(无列名)
select c1,c2 from t
*/