I get new applications to work on fairly regularly, and I have found that almost none of them have databases that are normalized to the third normal form. (Shocking, I know.) One that I recently inherited appears to store email addresses in multiple tables instead of in a single table with the user’s other information. This particular gem was sending out emails even to people who thought they had unsubscribed from its notifications because of the classic update anomalies that can result in storing data in multiple places.
I decided to piece together a script for looking at all text columns in the MS SQL Server database to see where else this user’s email address might be stored. Here’s what I came up with.
UPDATE: Revised script for SQL 2000 and user-defined data types
-- Set database to search
USE dbname
GO
------------------------------------------------------------------------------------------------------------------------------------
-- Set test value here and set @SearchViews to 1 if views are to be included - 0 if not; no other code below changes
DECLARE @TestValue NVARCHAR(MAX) = 'text to search'
DECLARE @SearchViews BIT = 0
------------------------------------------------------------------------------------------------------------------------------------
DECLARE @SQL NVARCHAR(MAX)
DECLARE @varSQL NVARCHAR(MAX)
DECLARE @WhereClause NVARCHAR(MAX)
DECLARE @TableName NVARCHAR(128)
DECLARE @varTableName NVARCHAR(128)
DECLARE @TableSchema NVARCHAR(128)
DECLARE @ColumnName NVARCHAR(128)
DECLARE @SchemaObjects TABLE (
TABLE_NAME NVARCHAR(128),
TABLE_SCHEMA NVARCHAR(128)
)
INSERT INTO @SchemaObjects
SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
IF @SearchViews = 1
BEGIN
INSERT INTO @SchemaObjects
SELECT TABLE_NAME, TABLE_SCHEMA FROM INFORMATION_SCHEMA.VIEWS
END
DECLARE TableCursor CURSOR FAST_FORWARD FOR
SELECT TABLE_NAME, TABLE_SCHEMA FROM @SchemaObjects
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
WHILE @@FETCH_STATUS = 0
BEGIN
SET @WhereClause = ''
DECLARE ColumnCursor CURSOR FAST_FORWARD FOR
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND TABLE_SCHEMA = @TableSchema AND ((DATA_TYPE IN ('CHAR','NCHAR','VARCHAR','NVARCHAR')) AND (CHARACTER_MAXIMUM_LENGTH IS NOT NULL) AND (CHARACTER_MAXIMUM_LENGTH >= LEN(@TestValue)) OR DATA_TYPE IN ('TEXT','NTEXT'))
OPEN ColumnCursor
FETCH NEXT FROM ColumnCursor INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
IF LEN(@WhereClause) > 0 SET @WhereClause = @WhereClause + ' OR '
SET @WhereClause = @WhereClause + '(CONVERT(NVARCHAR,[' + @ColumnName + ']) LIKE ''%' + @TestValue + '%'')'
FETCH NEXT FROM ColumnCursor INTO @ColumnName
END
CLOSE ColumnCursor
DEALLOCATE ColumnCursor
IF LEN(@WhereClause) > 0
BEGIN
SET @varTableName = REPLACE(@TableName,' ','_')
SET @varSQL = 'DECLARE @' + @varTableName + CHAR(10) + ' TABLE (' + CHAR(10)
SELECT @varSQL = @varSQL + ' ' + sc.name + ' ' +
CASE WHEN st.name = 'TIMESTAMP' THEN 'DATETIME' ELSE UPPER(st.name) END +
CASE WHEN st.name IN ('VARCHAR','NVARCHAR','CHAR','NCHAR') THEN '(' +
CASE WHEN sc.[length] > 4000 THEN 'MAX' ELSE CAST(sc.[length] AS NVARCHAR) END + ') ' ELSE ' ' END +
CASE WHEN sc.isnullable = 1 THEN 'NULL' ELSE 'NOT NULL' END + ',' + CHAR(10)
FROM sysobjects so
JOIN syscolumns sc ON sc.id = so.id
JOIN systypes st ON st.xusertype = sc.xusertype
WHERE so.name = @TableName
ORDER BY sc.colid
SELECT @varSQL = SUBSTRING(@varSQL,1,LEN(@varSQL) - 2) + CHAR(10) + ');' + CHAR(10)
SET @varSQL = @varSQL + 'INSERT INTO @' + @varTableName + CHAR(10) + 'SELECT * FROM [' + @TableSchema + '].[' + @TableName + ']' + CHAR(10) + 'WHERE ' + @WhereClause + ';' + CHAR(10)
SET @varSQL = @varSQL + 'IF EXISTS(SELECT * FROM @' + @varTableName + ')' + CHAR(10) + 'SELECT ''[' + @TableSchema + '].[' + @TableName + ']'' AS [Table Schema/Table Name], * FROM @' + @varTableName + ';'
SET @SQL = 'SELECT ''[' + @TableSchema + '].[' + @TableName + ']'' AS [Table Schema/Table Name], * FROM [' + @TableSchema + '].[' + @TableName + '] WHERE ' + @WhereClause
PRINT @SQL
PRINT @varSQL
EXEC (@varSQL)
END
FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
END
CLOSE TableCursor
DEALLOCATE TableCursor
