SQL Script to Find Text in all Database Tables and Views

SQL Server logo

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

Exporting Data from Lotus Notes Databases

Exporting from Lotus Notes

Lotus Notes was innovative and, by most accounts, a great collaboration software in its heyday. Though there are still some 35 million users of Notes, there are regular rumors of its imminent demise.

Because of this, some organizations that have used Notes over the years may want to move to some other system, such as SharePoint. Getting data out of Notes for reporting or for use in a different system is not very straightforward, as there are several options depending on your needs.

The first is to connect Notes to SQL, which I covered back in June 2012.

Exporting from Lotus Notes
Exporting from Lotus Notes

Another is to export the data into Lotus 1-2-3, and then into Microsoft Excel, if need be. This is problematic for many Excel users today, as you cannot open Lotus 1-2-3 files with versions of Excel newer than Excel 2003.

Exporting view data into a comma separated value (.csv) file is another option. I have read that this particular option does not always work exactly right, especially if there are commas or some other special characters in the data, so YMMV.

The main problem with all of the above options is that you can only export data shown in a particular view with these methods. Unless you want to create (or already have) a view that has all document fields in the view, you won’t be able to see them all. If you want to export all the data in each document, you must export documents into what is called “Structured Text”.

Structured Text, while readable by text editors, cannot be directly imported into CSV or other formats using only Word or Excel. However, some tools been developed for this purpose. One example of such a tool is the Structured Text Parser (STP) written by James J. Schwaller. Tools such as these will make converting data from Lotus Notes into other formats or for use in other systems much simpler, and will not require hiring contractors or buying expensive tools that you will not need once your data is converted.

Good luck!

Do We Have to Use a SQL Cursor?

SQL Server logo

In migrating data from a legacy budget application to its replacement, my requirement was to take the first and last years in a budget from the old application and update the corresponding table in the “new” app table with those dates as the beginning and ending of the project. One possible solution is to use a SQL cursor to loop through each record. When there are relatively few records, and the databases/tables are in the same location, this is an easy choice as the performance hit will be minimal.

However, in my case, there were 10000 records and the servers were on separate LANs. The cursor below did produce the desired result in terms of data, but it was horribly slow. It would have taken hours to completely copy all of the data, given the locations of the servers and possibly not the best indexing on the databases. (I didn’t design the indices! 😀 )

DECLARE WidgetBudgetCursor CURSOR
FOR SELECT [PSProjectID],[WidgetID] FROM [LinkedServername].[DbName].[Owner].[TableName]
OPEN WidgetBudgetCursor
FETCH NEXT FROM WidgetBudgetCursor INTO @ProjectID, @WidgetID
WHILE @@FETCH_STATUS = 0
BEGIN
	-- ...

	DECLARE @BudgetYearMin INT
	DECLARE @BudgetYearMax INT

	SELECT @BudgetYearMin=MIN([BudgetYear]), @BudgetYearMax=MAX([BudgetYear]) FROM [tWidgetbudgetbreakdown]
		WHERE [RequestId] = @WidgetID

	UPDATE [LinkedServername].[DbName].[Owner].[TableName]
		SET [ProjectStartDate] = CONVERT(DATETIME, '01-01-' + CAST(@BudgetYearMin AS CHAR(4)) + ' 00:00:00')
		WHERE [ProjectID] = @ProjectID

	UPDATE [LinkedServername].[DbName].[Owner].[TableName]
		SET [InServiceDate] = CONVERT(DATETIME, '12-31-' + CAST(@BudgetYearMax AS CHAR(4)) + ' 23:59:59')
		WHERE [ProjectID] = @ProjectID		

	-- ...

	FETCH NEXT FROM WidgetBudgetCursor INTO @ProjectID, @WidgetID
END
CLOSE WidgetBudgetCursor
DEALLOCATE WidgetBudgetCursor

After some initial attempts to copy small portions of the data took much longer than I expected it to, I redesigned the queries to be updates on joined tables (see below). This was much faster than using the cursor. While I was not able to eliminate the cursor for some other required functions, moving these update queries out of the cursor made the data move use less resources and take much less time.

UPDATE pm2
	SET [ProjectStartDate] =
	a.MinBudgetYear FROM
	(SELECT CONVERT(DATETIME, '01-01-' + CAST(MIN(Widget.[BudgetYear]) AS CHAR(4)) + ' 00:00:00') AS MinBudgetYear, pm.[WidgetID] AS WidgetID
	FROM [LinkedServername].[DbName].[Owner].[TableName] pm INNER JOIN
	[tWidgetbudgetbreakdown] Widget ON pm.[WidgetID] = Widget.[RequestID] GROUP BY pm.[WidgetID] HAVING LEN(MIN(Widget.[BudgetYear]))=4) a INNER JOIN
	[LinkedServername].[DbName].[Owner].[TableName] pm2 ON a.[WidgetID] = pm2.[WidgetID]	

UPDATE pm2
	SET [InServiceDate] =
	a.MaxBudgetYear FROM
	(SELECT CONVERT(DATETIME, '12-31-' + CAST(MAX(Widget.[BudgetYear]) AS CHAR(4)) + ' 23:59:59') AS MaxBudgetYear, pm.[WidgetID] AS WidgetID
	FROM [LinkedServername].[DbName].[Owner].[TableName] pm INNER JOIN
	[tWidgetbudgetbreakdown] Widget ON pm.[WidgetID] = Widget.[RequestID] GROUP BY pm.[WidgetID] HAVING LEN(MAX(Widget.[BudgetYear]))=4) a INNER JOIN
	[LinkedServername].[DbName].[Owner].[TableName] pm2 ON a.[WidgetID] = pm2.[WidgetID]