≡ Menu

This is closely related to my last post, where we were searching for text in the data itself, but now we’re just looking to see if the text exists in the statements that create views or stored procedures. This has been tested only on SQL Server 2008 R2. If you do not have permission on the database to see the definitions of views or stored procedures, then you will get zero rows returned. Otherwise, the names of the views and stored procs containing the text in @TestValue will be listed here, along with their respective CREATE statements. This script should run much faster than the one in the above-referenced post since no cursors are being used here.

-- Set database to search
USE dbname
GO
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
------------------------------------------------------------------------------------------------------------------------------------
-- Set test value here
-- no other code below changes 

DECLARE @TestValue VARCHAR(MAX) 

-- If characters in @TestValue need to be escaped, use backslash
SET @TestValue = 'text to search for'
------------------------------------------------------------------------------------------------------------------------------------

SELECT DISTINCT 
    o.name AS [Object_Name],
    o.type_desc,
	m.[definition]
FROM sys.sql_modules m 
    INNER JOIN 
    sys.objects o 
        ON m.object_id = o.object_id
WHERE m.definition LIKE '%' + @TestValue + '%' ESCAPE '\';

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.