Select * EXCEPT

sql

dynamic-sql

sql-injection

database-security

byAnton Shumikhin·Jan 13, 2025

Strike the chord with dynamic SQL for SELECT * EXCEPT.

DECLARE @cols NVARCHAR(MAX) = (
  SELECT STRING_AGG(QUOTENAME(column_name), ', ') 
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE TABLE_NAME = 'YourTable' 
  AND COLUMN_NAME NOT IN ('ExcludedColumn1', 'ExcludedColumn2')
  FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
);

EXEC('SELECT ' + @cols + ' FROM YourTable'); -- Voila!! Magic happens here

Seize the column list, jettison undesired columns, and fetch the selection.

Deep Dive into Dynamic SQL

Dynamic SQL is your rescue ranger when creating SQL queries programmatically becomes a need, making it possible for you to exclude columns dynamically from a SELECT statement. But beware, with great power, comes great responsibility.

The Magic of System Tables and Schemas

To exorcise specific columns, you can conjure up system tables or chess with the INFORMATION_SCHEMA. System tables like sysobjects and syscolumns are your secret envoys providing metadata:

-- A little SQL Server voodoo
DECLARE @cols NVARCHAR(MAX);
SET @cols = '';
SELECT @cols = @cols + QUOTENAME(c.name) + ','
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.name = 'YourTable'
AND c.name NOT IN ('ExcludedColumn1', 'ExcludedColumn2');
SET @cols = LEFT(@cols, LEN(@cols) - 1);
EXEC('SELECT ' + @cols + ' FROM YourTable'); -- Shazam! And your data appears

Watch your step, adventurer!

Always tread lightly when using dynamic SQL! It can unleash dreadful security vulnerabilities such as SQL injection if you don’t weigh in precautions such as parameterization. Also, dynamic SQL has a bit of a dark side. It’s often elusive to maintain and debug than static SQL.

The Oath of Flexibility and Limitations

While dynamic SQL endows you with the tools for customizing queries, it also nudges you to remember that SQL isn't always as dynamic as you are. However, some databases like Google BigQuery, DuckDB, and others would pleasantly surprise you with their SELECT * EXCEPT aptitude. So if you can spare the dynamism, embrace the built-in features first.

Code Transformations and Nifty Tricks

Crystalizing a function or stored procedure

Why not transform your dynamic SQL into a function or stored procedure? This would streamline the process of executing such queries for end users:

-- Basic stored procedure example in SQL Server
CREATE PROCEDURE SelectWithoutColumns 
  @TableName NVARCHAR(128),
  @ExcludedColumns NVARCHAR(MAX)
AS
BEGIN
  DECLARE @DynamicQuery NVARCHAR(MAX);
  -- (SQL construction logic goes here) It's like playing LEGO, but with SQL.
  EXEC sp_executesql @DynamicQuery; -- Watch a SQL query pop out of thin air!
END

Debugging Scenarios

Debugging with dynamically excluded columns can be a catch, but you can create a view without binary large objects (BLOBs):

CREATE VIEW ViewWithoutBlobs AS
SELECT non_blob_column1, non_blob_column2
FROM YourTable; -- "Who you gonna call? Debuggers!"

Applying Non-SQL Server Strategies

Adventurous RDBMS users have their tools:

Oracle users can lean on polymorphic table functions.
DB2 hides specific columns with IMPLICITLY HIDDEN.

Just remember, these tricks come with their own spellbooks (read - documentation).

Advanced RDBMS Features

New SQL systems such as Snowflake and Databricks SQL support the * EXCEPT syntax, while DuckDB provides EXCLUDE and REPLACE options for flexible column selection.

Oracle and DB2 Magic

Use Oracle 18c's polymorphic table functions for dynamic column skipping. In DB2, set the HIDDEN attribute to a column to make it invisible to SELECT * queries.