≡ Menu

SQL Script to Find Text in all Database Tables and Views…Revised!

Almost a year ago, I had reason to write a SQL script that would search for text anywhere in a database.

Since then, I’ve had reason to modify and enhance it. The modification was done primarily to support SQL Server 2000, which I still have to support.

The enhancement was done primarily to support user-defined data types. Also, I added some error-handling.

In order to allow for up to 8000 characters in a variable on SQL 2000, I changed the NVARCHARs to VARCHAR, and MAX to 8000 in the variable declarations. If you want to use this script on SQL 2005 or newer, you can change these back to make the script more scalable.

-- Set database to search
USE dbname
GO
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
------------------------------------------------------------------------------------------------------------------------------------
-- Set test value here and set @SearchViews to 1 if views are to be included - 0 if not; no other code below changes
-- Set @PrintAllSQLStmts to 1 if you want all of the statements printed as messages
DECLARE @TestValue VARCHAR(8000) 
DECLARE @SearchViews BIT 
DECLARE @PrintAllSQLStmts BIT

SET @PrintAllSQLStmts = 0
SET @SearchViews = 0
SET @TestValue = 'text to search'
------------------------------------------------------------------------------------------------------------------------------------

/* If the version of SQL Server is 2005 or newer, these can be set to MAX rather than 8000 */
DECLARE @SQL VARCHAR(8000)
DECLARE @varSQL VARCHAR(8000)
DECLARE @WhereClause VARCHAR(8000)
----------------------------------------------------------------------------------------------

DECLARE @TableName VARCHAR(128)
DECLARE @varTableName VARCHAR(128)
DECLARE @TableSchema VARCHAR(128)
DECLARE @ColumnName VARCHAR(128)
DECLARE @MaxLength VARCHAR(4)
DECLARE @NMaxLength VARCHAR(4)

IF @@VERSION LIKE 'Microsoft SQL Server  2000%'
	BEGIN
		SET @MaxLength = '8000'		
		SET @NMaxLength = '4000'		
	END
ELSE
	BEGIN
		SET @MaxLength = 'MAX'
		SET @NMaxLength = 'MAX'
	END
	
DECLARE @SchemaObjects TABLE (
	TABLE_NAME VARCHAR(128), 
	TABLE_SCHEMA VARCHAR(128)
)

DECLARE @TypeTable TABLE (
	UserType VARCHAR(128) PRIMARY KEY,
	BaseType VARCHAR(128)
)

DECLARE @SizableTypes TABLE (
	TypeName VARCHAR(128) PRIMARY KEY
)

INSERT INTO @SizableTypes VALUES ('VARBINARY')
INSERT INTO @SizableTypes VALUES ('VARCHAR')
INSERT INTO @SizableTypes VALUES ('NVARCHAR')
INSERT INTO @SizableTypes VALUES ('CHAR')
INSERT INTO @SizableTypes VALUES ('NCHAR')

INSERT INTO @TypeTable
SELECT ut.name, bt.name FROM SysTypes ut, SysTypes bt
 WHERE ut.xusertype > 256 AND bt.xusertype < 256
 AND ut.xtype = bt.xtype

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(VARCHAR,[' + @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 + ' TABLE (' + CHAR(10) 		
		SELECT @varSQL = @varSQL + ' [' + sc.name + '] ' + 		
		CASE WHEN st.name = 'TIMESTAMP' THEN 'DATETIME' 			 
			 WHEN tt.UserType IS NOT NULL THEN UPPER(tt.BaseType) 			 
		ELSE UPPER(st.name) END +
		CASE WHEN tt.BaseType IN (SELECT TypeName FROM @SizableTypes) OR st.name IN (SELECT TypeName FROM @SizableTypes) THEN '(' + 
			CASE WHEN (LEFT(tt.BaseType,3) = 'VAR' OR LEFT(st.name,3) = 'VAR') AND sc.[length] > 8000 THEN @MaxLength
			WHEN (LEFT(tt.BaseType,3) = 'NVA' OR LEFT(st.name,3) = 'NVA') AND sc.[length] > 4000 THEN @NMaxLength
			ELSE CAST(sc.[length] AS VARCHAR) 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
		LEFT OUTER JOIN @TypeTable tt ON st.name = tt.UserType
		WHERE so.name = @TableName
		ORDER BY sc.colid		
		
		SET @SQL =  CHAR(10) + 'SELECT ''[' + @TableSchema + '].[' + @TableName + ']'' AS [Table Schema/Table Name], * FROM [' + @TableSchema + '].[' + @TableName + '] WHERE ' + @WhereClause + ';' 
		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) 
		SET @varSQL = @varSQL + 'BEGIN' + CHAR(10)
		SET @varSQL = @varSQL + '   SET NOCOUNT OFF;' + CHAR(10)		
		
		IF @PrintAllSQLStmts = 0
			SET @varSQL = @varSQL + '   PRINT ''' + CHAR(10) + CHAR(10) + CHAR(10) + '/* New Query */' + CHAR(10) + REPLACE(@SQL,'''','''''') + ''';'  + CHAR(10)
		ELSE
			PRINT CHAR(10) + CHAR(10) + CHAR(10) + '/* New Query */' + @SQL + CHAR(10)		

		SET @varSQL = @varSQL + '   SELECT ''[' + @TableSchema + '].[' + @TableName + ']'' AS [Table Schema/Table Name], * FROM @' + @varTableName + ';' + CHAR(10)		
		
		SET @varSQL = @varSQL + '   SET NOCOUNT ON;' + CHAR(10)
		SET @varSQL = @varSQL + 'END' + CHAR(10)
		SET @varSQL = @varSQL + CHAR(10) + CHAR(10) + CHAR(10)
		--PRINT @varSQL
		EXEC (@varSQL)
		IF @@ERROR <> 0
		BEGIN 
			PRINT '/* Statement causing error */' + CHAR(10) + CHAR(10) + @varSQL + CHAR(10) + '/* End Statement */' + CHAR(10) 
			PRINT 'Length of SQL statement: ' + CONVERT(VARCHAR,LEN(@varSQL)) + CHAR(10)
		END
	END
	FETCH NEXT FROM TableCursor INTO @TableName, @TableSchema
END
CLOSE TableCursor
DEALLOCATE TableCursor

Code School Classes Are Complete…For Now…

Over the course of the last year, I’ve been slowly making my way through all of the Code School Paths, and I finally finished the iOS Path today.

My Code School Report Card

My Code School Report Card, as of 6-22-2015

I saved iOS for the end, in the hopes that the Objective C classes would be retired in favor of newer Swift classes, but those haven’t even been published yet.

After a year of classes, I maintain that Code School is well worth the price tag ($29/month or $290/year) as long as you stay committed to a schedule of study. It’s a wealth of information for this price, but you won’t learn it unless you do it.

This past year, Code School was purchased by Pluralsight, so I only expect the classes and class offerings to continue to improve. I’m still waiting on that Python path, which I hear is in development.

While I’m also waiting on Swift classes from Code School, I plan to take the iOS 8 Immersive course, which covers Swift, at bitfountain.io. For $149, it’s quite reasonable, as long as it delivers what is promised.

Nothing to See Here… Move Along

In my 30-plus years playing and working with computers, I’ve seen some strange things. This may be the first time I’ve actually seen an automated application installer ask me, as part of its “automatic” installation process, to manually copy files from one folder it just created, to another folder it just created.

Kony Studio installer dialog box

Wonders never cease.

The most widely known way (which is also often described as the best way) to learn Ruby on Rails is the Rails Tutorial by Michael Hartl. Its third edition, which covers Rails 4, has been available as both a digital book and video screencasts for several months on Hartl’s Rails Tutorial website, but was just released in print form yesterday. I received mine from Amazon today, and was pleased to find my review (of the Second Edition, though it certainly holds true for the Third even more so!) printed just before the title page.

Quick Fixes on SSIS with Oracle Data Sources

I’m currently in the process of building out an ODS (Operational Data Store) that will integrate data from multiple systems and make that data available for reporting using Spotfire and other BI tools.

Both the ODS DBMS and the first system that will be used as a source for the data in the ODS run on Oracle Database. I am using Visual Studio Ultimate 2013 64-bit to build an SSIS package to flow the data from the source into the ODS.

In debugging the SSIS package, I’ve run into a few things that make troubleshooting much easier. The first thing to know is that the Oracle Database is a 32-bit system. The first thing that must be done after loading the SSIS package into Visual Studio is making sure that the Run64BitRuntime setting is set to False in the project’s Properties box. (This can be found in the PROJECT menu.)

Since the data sources for the ODS are Oracle, the SQL queries used to pull data must conform to Oracle syntax. To prevent things like SSIS automatically adding semicolons to the queries (and preventing me from adding them myself), I set the BypassPrepare property to True. This should allow the query to run the same way in SSIS as it does in SQL Developer. (If this is set to False, SSIS will parse the query rather than passing it to Oracle.) This setting made writing queries much easier, as I could test them in SQL Developer and paste them into SSIS without having to rewrite them to conform to SSIS.