Thursday, October 27, 2011

Find column names which have Special characters

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.COLUMN_NAME LIKE '%[^a-zA-Z0-9/_]%'
USE[]
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]%'

No comments:

Post a Comment