How to Select all (*) the column Except Some specified columns

This Procedure Helps to Retrieve all the column data from a table excluding some specified columns.

Here i created a procedure named as "EXCEPT_COLUMNS"

It Accepts following 3 sort of Parameters

· Table Name

· List of columns to Exclude (pass column name with comma(,) separated)

· Filter Condition if Any else pass Empty string('')

First Create the follwing procedure

SP Source :.

CREATE PROCEDURE EXCEPT_COLUMNS

(

@TABLE_NAME VARCHAR(50),

@COLUMNSLISTTOREMOVE VARCHAR(500),

@Filter varchar(5000)

)

AS

BEGIN

DECLARE @TEMP VARCHAR(50)

DECLARE @FINAL_COLUMNLIST VARCHAR(50)

DECLARE @QUERY VARCHAR(500)

SET @COLUMNSLISTTOREMOVE = REPLACE(@COLUMNSLISTTOREMOVE,',',''',''')

SET @COLUMNSLISTTOREMOVE = ''''+ @COLUMNSLISTTOREMOVE + ''''

SET @FINAL_COLUMNLIST = ''

SET @QUERY = 'DECLARE C1 CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME =''' + @TABLE_NAME + ''' AND COLUMN_NAME NOT IN ('+@COLUMNSLISTTOREMOVE+')'

EXECUTE(@QUERY)

OPEN C1

FETCH NEXT FROM C1 INTO @TEMP

WHILE @@FETCH_STATUS =0

BEGIN

IF @FINAL_COLUMNLIST = ''

BEGIN

SET @FINAL_COLUMNLIST =@TEMP

END

ELSE

BEGIN

SET @FINAL_COLUMNLIST = @FINAL_COLUMNLIST + ',' + @TEMP

END

FETCH NEXT FROM C1 INTO @TEMP

END

CLOSE C1

DEALLOCATE C1

--SET @CList = @FINAL_COLUMNLIST

SET @QUERY = 'SELECT ' + @FINAL_COLUMNLIST + ' FROM ' + @TABLE_NAME

IF @Filter <> ''

BEGIN

SET @QUERY = @QUERY + ' WHERE ' + @Filter

END

EXECUTE(@QUERY)

END

Sample SQL Script for Execute this Procedure:.

Sample 1:

Table CAREERINFO Contains following Columns:

EmployeeNo

CompanyName

JobTitle

StartDate

EndDate

Remarks

EXECUTE EXCEPT_COLUMNS 'CAREERINFO','REMARKS,ENDDATE',''

Output for this Query:

EmployeeNo CompanyName JobTitle StartDate

----------------------------------------------

510101 IGNIS ACCO 2006-06-18 00:00:00.000

Sample 2:

Table SystemCodes Contains following Columns

TypeCode

TypeDesc

SystemCode

CodeDesc

ActiveStatus

EXECUTE EXCEPT_COLUMNS 'SystemCodes','ActiveStatus','TYPEDESC = ''DECIPLINE'''

Output for this Query:

TypeCode TypeDesc SystemCode CodeDesc

----------------------------------------------

DCPL DECIPLINE 01 ELECTRICAL

DCPL DECIPLINE 02 MECHANICAL

DCPL DECIPLINE 03 CIVIL

DCPL DECIPLINE 04 INSTRUMENTATION

DCPL DECIPLINE 05 PETRO CHEMICAL

DCPL DECIPLINE 06 TELECOMM

DCPL DECIPLINE 07 ADMIN

DCPL DECIPLINE 08 SERVICES

DCPL DECIPLINE 09 IT

Note: If you want only the list of columns after excluded, remove the filter condition, then add a output columns which can be accessed outside of this procedure

Hope it Helps you. Leave your valuable comments