T-SQL: Create Dynamic SQL Statement using XMLPATH() and sp_executesql

In this post I would like to demonstrate a way how to exlude columns from a Select * query and executing the new query dynamically with the built-in procedure sp_executesql.
At work I had a request, where I had to exclude a certain column, which is usually no problem. The issue was, that I had a table that was being altered constantly and new columns where added to it from time to time. So simply writing the desired columns would have led to the result that new columns wouldn't be selected at runtime.
With the help of XMLPATH() and sp_executesql I was able to solve this issue. Let's look at the following table:

The table is called rec.DimCountries. Let's assume we will be adding a new column to it next week and another one a week later and so on. The end user would like to see every column except for CountryID.

rec.DimCountries
CountryID
Country
Name

With the help of sys.columns I am able to get all columns listed, but first we need to find out the object_id of the table in sys.objects

SELECT object_id
FROM sys.objects
WHERE name = 'DimCountries'

 object_id
-----------
517576882

Now we can lookup the column names from sys.columns with that object_id

select name
from sys.columns
where object_id = 517576882

 name
-----------
CountryID
Country
Name

So this gives us the chance to exclude 'CountryID' and lookup the rest dynamically.
I will add a comma for listing purposes here:

SELECT ', ' + Name
FROM sys.columns
where object_id = (
SELECT object_id
FROM sys.objects
WHERE name = 'DimCountries')
AND name <> 'CountryID'

-----------
, Country
, Name

I need to get this result above in one line and remove the first comma. For the removal I will use STUFF and for the one line listing I will use XMLPATH().
Here is the complete script. I will break down the main elements below:


DECLARE @cols nvarchar(200),
@sqlStatement nvarchar(300)

SELECT @cols = STUFF((
            SELECT ', ' + Name
            FROM sys.columns
            where object_id = (
                SELECT object_id
                FROM sys.objects
                WHERE name = 'DimCountries')
                AND name <> 'CountryID'
                FOR XML PATH('')
                ), 1, 1, '' )

SET @sqlStatement = N'SELECT ' + @cols + N' FROM rec.DimCountries'
EXEC sp_executesql @sqlStatement


FOR XML PATH('') will concat the results of the column name into 1 line

STUFF(', Country, Name', 1, 1, '') will remove the first comma. The first part has the value that needs to be changed, second part determines where to start, the third part is the length and fourth part is what to value to use for replacement:

STUFF ( character_expression , start , length , replaceWith_expression )


The output of @cols = Country, Name
So I will create a string for the SELECT part and assign it to variable @sqlStatement

Now we can hand over the variable @sqlStatement to sp_executesql
EXEC sp_executesql @sqlStatement 

Result delivers dynamically every column except CountryID
Country  Name
---------------------------
JPN      Japan
EU       European Union
A         Austria
ITA      Italy