There are times where I've needed to build a query that includes a long list of columns and I've needed to apply a similar set of functions to many of these columns according to their data type. For instance, I've needed to trim the leading and trailing spaces from all the character string columns (like varchar or nvarchar). I want to show a technique of targeting all the columns of a certain data type and creating the same expressions for them in the query. The information needed is found in the INFORMATION_SCHEMA.COLUMNS view.

Let's say that I want to trim leading and trailing spaces using the LTRIM() and RTRIM() functions for all the character string data type columns in the query (like varchar and nvarchar). I want to handle nulls with the ISNULL() function only if the column allows nulls. I want to cast columns that are date data types (like datetime) to varchar(max). Rather than do all this by hand, I'll use the technique where I generate the expressions using a query.

Here is a table definition for my demo table to show the query-generated column expressions technique.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FictionalEmployees]') AND type in (N'U'))
DROP TABLE [dbo].[FictionalEmployees]
GO
CREATE TABLE [dbo].[FictionalEmployees](
 [employeeid] [int] IDENTITY(1,1) NOT NULL,
 [lastname] [nvarchar](20) NOT NULL,
 [firstname] [nvarchar](10) NULL,
 [title] [nvarchar](30) NOT NULL,
 [titleofcourtesy] [nvarchar](25) NOT NULL,
 [dateofbirth] [datetime] NOT NULL,
 [dateofhire] [datetime] NOT NULL,
 [address] [nvarchar](60) NOT NULL,
 [city] [nvarchar](15) NOT NULL,
 [region] [nvarchar](15) NULL,
 [postalcode] [nvarchar](10) NULL,
 [country] [nvarchar](15) NOT NULL,
 [phone] [nvarchar](24) NOT NULL,
 [lifestory] [varchar](MAX) NULL,
 [managerid] [int] NULL)
ON [PRIMARY];
GO

The demo table has got lots of character string-based data types (varchar, nvarchar), a couple of date and time data types (datetime) and a couple of exact numeric data types (int). The next step is to identify an expression for each common type to apply.

To organize these column expressions, I'll create a table where I can associate each expression with a data type and the column nullability.

CREATE TABLE #ExpressionToDataTypeAndNull
(
DATA_TYPE NVARCHAR(128),
IS_NULLABLE VARCHAR(3),
CustomExpression VARCHAR(MAX) 
);

The columns DATA_TYPE and IS_NULLABLE match up with columns found in the INFORMATION_SCHEMA.COLUMNS view. INFORMATION_SCHEMA views are an ISO standard for providing metadata about objects in the database such as tables and columns. As Joe Celko puts it (in paraphrase), this is SQL describing itself.

The column CustomExpression is where I'll put the expressions common to the data type and nullability that I want to target.

BOL tells me that IS_NULLABLE contains values "YES" or "NO". Values for DATA_TYPE come from the "name" column of the sys.types table as in:

SELECT name FROM sys.types;

So for the demo, here's the values for #ExpressionToDataTypeAndNull:

INSERT INTO #ExpressionToDataTypeAndNull
 ( DATA_TYPE ,
 IS_NULLABLE ,
 CustomExpression
 )
VALUES 
( 'varchar','YES','LTRIM(RTRIM(ISNULL("<column>",''''))) AS "<column>",')
,( 'varchar','NO','LTRIM(RTRIM("<column>")) AS "<column>",')
,( 'nvarchar','YES','LTRIM(RTRIM(ISNULL("<column>",''''))) AS "<column>",')
,( 'nvarchar','NO','LTRIM(RTRIM("<column>")) AS "<column>",')
,( 'datetime','YES','CAST("<column>" AS varchar(max)) AS "<column>",')
,( 'datetime','NO','CAST("<column>" AS varchar(max)) AS "<column>",');

SQL Server 2008 allows for a comma-delimited values list to expedite multiple row inserts in the same statement.

Notes:

I'm using quotes as in "" rather than brackets [ ] just to show quotes are the SQL standard.

I use a string token that will be replaced with the name of the column using the REPLACE() function.

For the data types varchar and nvarchar, the custom expression trims leading and trailing spaces with the LTRIM() and RTRIM() functions. For those that are nullable, the ISNULL() function is applied. The columns are aliased with the AS clause.

I didn't target the columns with int data type on purpose to make sure the technique will include all the columns in the query regardless of whether or not we get a match from the #ExpressionToDataTypeAndNull table.

To find the data type and the nullability for each column in the query of the FictionalEmployees table, I use the INFORMATION_SCHEMA.COLUMNS view. Here's the query that generates the results for the technique:

DECLARE @TableName nvarchar(128)
SET @TableName = 'FictionalEmployees'
SELECT
   ISNULL(REPLACE(CustomExpression,'<column>', COLUMN_NAME), COLUMN_NAME + ' AS ' + COLUMN_NAME + ',') AS ThisColumnExpression
FROM INFORMATION_SCHEMA.COLUMNS a
FULL OUTER JOIN #ExpressionToDataTypeAndNull b
ON a.DATA_TYPE = b.DATA_TYPE
AND a.IS_NULLABLE = b.IS_NULLABLE
WHERE TABLE_NAME = @TableName
ORDER BY a.ordinal_position ASC;

FULL OUTER JOIN allows the query to return columns that don't have a match in the #ExpressionToDataTypeAndNull table. There isn't a match for the int data type, so I want a default to apply. Since FULL OUTER JOIN returns NULL when there's no match in the join, I can use ISNULL() to apply the default.

The results:

employeeid AS employeeid,
LTRIM(RTRIM("lastname")) AS "lastname",
LTRIM(RTRIM("firstname")) AS "firstname",
LTRIM(RTRIM("title")) AS "title",
LTRIM(RTRIM("titleofcourtesy")) AS "titleofcourtesy",
CAST("dateofbirth" AS varchar(max)) AS "dateofbirth",
CAST("dateofhire" AS varchar(max)) AS "dateofhire",
LTRIM(RTRIM("address")) AS "address",
LTRIM(RTRIM("city")) AS "city",
LTRIM(RTRIM(ISNULL("region",''))) AS "region",
LTRIM(RTRIM(ISNULL("postalcode",''))) AS "postalcode",
LTRIM(RTRIM("country")) AS "country",
LTRIM(RTRIM("phone")) AS "phone",
LTRIM(RTRIM("lifestory")) AS "lifestory",
managerid AS managerid,

So here are the query-generated column expressions. Notice all the columns are there including the int data type columns even though there were no rows set up in the expressions table for them. The idea is to copy the results into a text editor, remove the last comma, sandwich the expressions with a SELECT clause and a FROM clause as in "FROM FictionalEmployees" and you've got an executable query that you can then go and copy into Management Studio and execute.

So there's some obvious up-front work to do - but the set-up isn't bad. After that, it can start paying dividends. Aside from that, I'd be curious to see what custom expressions you might want to plug in that you might use often. And, I'd like to see other techniques you're using INFORMATION_SCHEMA.COLUMNS in.