Simple Technique to Exclude Column from SELECT * FROM TABLENAME

Introduction

Writing SELECT statement to retrieve data is a regular task in our development life.

Most of the time we use SELECT * to avoid the hassle of  writing all the column name of a table, specially for a table with too many columns. There is some cases where we need to exclude very few columns from the select * statement. There is no state forward way in SQL server to do this other than writing the desired comma separated column names in the select statement. 

Background

As I am working on a project which is now in maintenance phase and I take care of data checking issues using the select statement. One big table that i look for data has a column of data type XML and has more than 40 columns in that table. I need all the columns except the XML one which decrease my query performance. Then i write down a utility function that takes the table name and the column names in comma separated string as parameter and returns the select statement with my desired columns excluding the unwanted columns.

Using the code 

3 simple steps to do this:

Step 1: At first create the following split function in your Database

Split is Table-valued function which will help us to split comma-separated (or any other delimiter value) string into individual string.

CREATE FUNCTION dbo.Split
(@String VARCHAR (8000), @Delimiter CHAR (1))
RETURNS 
    @temptable TABLE (
        items VARCHAR (8000))
AS
BEGIN
    DECLARE @idx AS INT;
    DECLARE @slice AS VARCHAR (8000);
    SELECT @idx = 1;
    IF len(@String) < 1
       OR @String IS NULL
        RETURN;
    WHILE @idx != 0
        BEGIN
            SET @idx = charindex(@Delimiter, @String);
            IF @idx != 0
                SET @slice = LEFT(@String, @idx - 1);
            ELSE
                SET @slice = @String;
            IF (len(@slice) > 0)
                INSERT  INTO @temptable (Items)
                VALUES                 (@slice);
            SET @String = RIGHT(@String, len(@String) - @idx);
            IF len(@String) = 0
                BREAK;
        END
    RETURN;
END 

Step 2: Then create the following function to get the desired select statement

Get_Desired_Select_Statement is a scalared valued function which will take the table name and the column names as parameter and will return a select statemet string excluding the columns

CREATE FUNCTION [dbo].[Get_Desired_Select_Statement]
(@tableName VARCHAR (200), @columnNames VARCHAR (500))
RETURNS VARCHAR (MAX)
AS
BEGIN
    DECLARE @Columns AS VARCHAR (MAX);
    SET @Columns = (SELECT SUBSTRING((SELECT   ',' + t.COLUMN_NAME
                                      FROM     INFORMATION_SCHEMA.COLUMNS AS t
                                      WHERE    TABLE_NAME = @tableName
                                               AND COLUMN_NAME NOT IN (SELECT *
                                                                       FROM   dbo.Split (@columnNames, ','))
                                      ORDER BY ORDINAL_POSITION
                                      FOR      XML PATH ('')), 2, 200000) AS CSV);
    RETURN ('SELECT ' + @Columns + ' FROM ' + @tableName);
END

step 3: Use the Get_Desired_Select_Statement  function

DECLARE @sql VARCHAR(max) = (select dbo.Get_Desired_Select_Statement('TABLENAME','Col1,Col2'))

EXEC(@sql)

This will return data with all the colums of TABLENAME excluding 'Col1,Col2'. 

 Example:
 

SELECT * FROM EMPLOYEES  

This statement returns

DECLARE @sql VARCHAR(max) = (select dbo.Get_Desired_Select_Statement('EMPLOYEES','hid,lvl'))

EXEC(@sql)

This will return 

Hope this will help you.