The following script helps to find columns which have special characters other than alphanumeric, in a given database. If some special character for example under score "_" as part of column name and want to ignore, include special character with escape character as below in regular expression.

col.COLUMNNAME LIKE '%[^a-zA-Z0-9/]%'

USE[<database name>]
GO

SELECT  tab.TABLE_NAME,col.COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS col
INNER   JOIN INFORMATION_SCHEMA.TABLES tab
    	ON tab.Table_name = col.TABLE_NAME
WHERE   tab.TABLE_TYPE = 'BASE TABLE'	
    	AND
    	col.COLUMN_NAME  LIKE '%[^a-zA-Z0-9]%'

Published under: SQL Server Tips ·  ·  ·  ·