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.