Monday, September 6, 2010

EXCLUDE COLUMN IN SQL SERVER

In one of my project i want a select query on table which have bunch of columns and want to exclude a column "Reason" from them.

Query

DECLARE @tableName SYSNAME,


@excludeColumnName SYSNAME,

@whereClause VARCHAR(MAX)



SET @tableName = 'EXPCONTACT'

SET @excludeColumnName = 'REASON'



DECLARE @query NVARCHAR(MAX)

DECLARE @select VARCHAR(MAX)



select @select = COALESCE(@select + ',','') + COLUMN_NAME

from INFORMATION_SCHEMA.COLUMNS

where TABLE_NAME = @tableName and COLUMN_NAME <> @excludeColumnName

order by ORDINAL_POSITION



set @query = 'SELECT ' + @select + char(13) +

'FROM ' + UPPER(@tableName) +

case when @whereClause is not null then char(13) + 'where ' + @whereClause else '' end



print @query

exec sp_executesql @query

2 comments: