Search entire DB for a string....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Search entire DB for a string....

Author

Topic

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-09-23 : 07:16:00

Anyone have a script/proc for this for 2k5?

Thanks,

Jim

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 07:32:03

Here's mine. Uses LIKE and a databasemask so you can check multiple databases.

Should be pretty self explanatory



/*** Search Databases / Tables for a string ***********************************
*
*
* Charlie (2010-Feb-02)
*
******************************************************************************/

DECLARE @searchFor VARCHAR(255)        SET @searchFor = '<Your Text Here>'

DECLARE @dbMask VARCHAR(255)           SET @dbMask = '<DBNAME OR PATTERN>'
DECLARE @includeUnderscoreTables BIT   SET @includeUnderscoreTables = 0

DECLARE @debug BIT                     SET @debug = 0

/*****************************************************************************/

SET NOCOUNT ON

-- Table that holds the list of tables to ignore
IF OBJECT_ID('tempdb..#ignore') IS NOT NULL DROP TABLE #ignore
CREATE TABLE #ignore (
	[TABLE_NAME] VARCHAR(255) PRIMARY KEY
	)

-- Populate ignore table
-- INSERT #ignore ([TABLE_NAME])

-- Results table
IF object_Id('tempDb..#found') IS NOT NULL DROP TABLE #found
CREATE TABLE #found (
	[databaseName] NVARCHAR(255)
	, [tableName] NVARCHAR(255)
	, [columnName] NVARCHAR(255)
	, [Id] NVARCHAR(255)
	, [value] NVARCHAR(MAX)
	)

-- SeachList
IF object_Id('tempDb..#searchList') IS NOT NULL DROP TABLE #searchList
CREATE TABLE #searchList (
	[TABLE_CATALOG] NVARCHAR(255)
	, [TABLE_SCHEMA] NVARCHAR(255)
	, [TABLE_NAME] NVARCHAR(255)
	, [COLUMN_NAME] NVARCHAR(255)
	, [COLLATION_NAME] NVARCHAR(255)
	, [DATA_TYPE] NVARCHAR(255)
	, [ID_FIELD] BIT DEFAULT 0
	)


-- Variables
DECLARE @sql NVARCHAR(MAX)
DECLARE @database VARCHAR(255)
DECLARE @tableName VARCHAR(255)
DECLARE @columnName VARCHAR(255)
DECLARE @schema VARCHAR(255)
DECLARE @dataType VARCHAR(255)
DECLARE @collation VARCHAR(255)
DECLARE @idField BIT
DECLARE @msgText VARCHAR(300)
DECLARE @now DATETIME


-- Populate the SearchList
DECLARE databaseCursor CURSOR LOCAL READ_ONLY FOR SELECT
	[name]
FROM
	sys.databases
WHERE
	[name] LIKE @dbMask

OPEN databaseCursor

	FETCH NEXT FROM databaseCursor INTO @database

	WHILE ( @@FETCH_STATUS = 0 ) BEGIN

		SET @msgText = 'Scanning ' + QUOTENAME(@database) + ' for target columns'
		RAISERROR(@msgText, 0, 1) WITH NOWAIT

		SET @sql = N'
		INSERT INTO #searchList (
			[TABLE_CATALOG]
			, [TABLE_SCHEMA]
			, [TABLE_NAME]
			, [COLUMN_NAME]
			, [COLLATION_NAME]
			, [DATA_TYPE]
			)
		SELECT
			QUOTENAME([TABLE_CATALOG])
			, QUOTENAME([TABLE_SCHEMA])
			, QUOTENAME([TABLE_NAME])
			, QUOTENAME([COLUMN_NAME])
			, [COLLATION_NAME]
			, [DATA_TYPE]
		FROM
			' + QUOTENAME(@database) + '.information_schema.columns ic
		WHERE
			ic.[DATA_TYPE] IN (
				''VARCHAR''
				, ''TEXT''
				, ''CHAR''
				, ''NVARCHAR''
				, ''NCHAR''
				, ''NTEXT''
			)

			AND (
				ic.[TABLE_NAME] NOT LIKE ''[_]%''
				OR @includeUnderscoreTables = 1
				)

			AND NOT EXISTS (
				SELECT 1
				FROM
					#ignore i
				WHERE
					i.[TABLE_NAME] = ic.[TABLE_NAME] COLLATE DATABASE_DEFAULT
				)

		UPDATE sl
		SET
			[ID_FIELD] = 1
		FROM
			#searchList sl
			JOIN ' + QUOTENAME(@database) + '.information_schema.columns isc ON
				QUOTENAME(isc.[TABLE_CATALOG]) = sl.[TABLE_CATALOG] COLLATE DATABASE_DEFAULT
				AND QUOTENAME(isc.[TABLE_SCHEMA]) = sl.[TABLE_SCHEMA] COLLATE DATABASE_DEFAULT
				AND QUOTENAME(isc.[TABLE_NAME]) = sl.[TABLE_NAME] COLLATE DATABASE_DEFAULT
				AND isc.[COLUMN_NAME] = ''Id'' COLLATE DATABASE_DEFAULT'

		IF @debug = 1 PRINT @sql

		EXEC sp_ExecuteSql
			@sql
			, N'@includeUnderscoreTables BIT
			, @database VARCHAR(255)'
			, @includeUNderscoreTables
			, @database

		FETCH NEXT FROM databaseCursor INTO @database
	END
CLOSE databaseCursor
DEALLOCATE databaseCursor

RAISERROR('', 0, 1) WITH NOWAIT

SET @msgText = 'Starting Search for string : ' + @searchFor
RAISERROR(@msgText, 0, 1) WITH NOWAIT

RAISERROR('', 0, 1) WITH NOWAIT

-- Now serach through the list. This can take a while!
DECLARE searchCursor CURSOR LOCAL READ_ONLY FOR
SELECT
	[TABLE_CATALOG]
	, [TABLE_SCHEMA]
	, [TABLE_NAME]
	, [COLUMN_NAME]
	, [DATA_TYPE]
	, [COLLATION_NAME]
	, [ID_FIELD]
FROM
	#searchList
ORDER BY
	[TABLE_CATALOG]
	, [TABLE_SCHEMA]
	, [TABLE_NAME]
	, [COLUMN_NAME]

OPEN searchCursor

	FETCH NEXT FROM searchCursor INTO
		@database
		, @schema
		, @tableName
		, @columnName
		, @dataType
		, @collation
		, @IdField

	WHILE ( @@FETCH_STATUS = 0 ) BEGIN

		SET @now = GETDATE()
		SET @msgText =
			CONVERT(CHAR(11), @now, 106) + ' @ '
			+ CONVERT(CHAR(8), @now, 108)
			+ ' - Checking.... '
			+ @database +
			'.'
			+ @schema
			+
			'.'
			+ @tableName
			+ '.'
			+ @columnName

		RAISERROR(@msgText, 0 , 1) WITH NOWAIT
			
		SET @sql = N'
		INSERT INTO #found (
			[databaseName]
			, [tableName]
			, [columnName]
			, [Id]
			, [value]
			)
		SELECT
			@database
			, @tableName
			, @columnName
			, ' + CASE @IdField WHEN 1 THEN '[Id]' ELSE 'NULL' END + '
			, ' + @columnName + '
		FROM
			' + @database + '.' + @schema + '.' + @tableName + ' WITH (NOLOCK)
		WHERE
			' + @columnName + ' LIKE @searchFor'

		-- Show and execute the sql
		IF @debug = 1 PRINT @sql

		EXEC sp_executeSql
			@sql
			, N'@database NVARCHAR(255)
			, @tableName NVARCHAR(255)
			, @columnName NVARCHAR(255)
			, @searchFor NVARCHAR(255)'
			, @database
			, @tableName
			, @columnName
			, searchFor

		FETCH NEXT FROM searchCursor INTO
			@database
			, @schema
			, @tableName
			, @columnName
			, @dataType
			, @collation
			, @IdField

	END

CLOSE searchCursor
DEALLOCATE searchCursor

SELECT * FROM #found

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-09-23 : 08:22:53

`Hi Charlie,

inserted the Db name and string data into the variables, but the error message reads,

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@searchFor". ???

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 09:19:23

hmmmm.. OK. I think I see where the problem is:

This works for me


/*** Search Databases / Tables for a string ***********************************
*
*
* Charlie (2010-Feb-02)
*
******************************************************************************/

DECLARE @searchFor VARCHAR(255)        SET @searchFor = '%foo%'

DECLARE @dbMask VARCHAR(255)           SET @dbMask = 'ReFlex_Main'
DECLARE @includeUnderscoreTables BIT   SET @includeUnderscoreTables = 0

DECLARE @debug BIT                     SET @debug = 0

/*****************************************************************************/

SET NOCOUNT ON

-- Table that holds the list of tables to ignore
IF OBJECT_ID('tempdb..#ignore') IS NOT NULL DROP TABLE #ignore
CREATE TABLE #ignore (
	[TABLE_NAME] VARCHAR(255) PRIMARY KEY
	)

-- Populate ignore table
-- INSERT #ignore ([TABLE_NAME])

-- Results table
IF object_Id('tempDb..#found') IS NOT NULL DROP TABLE #found
CREATE TABLE #found (
	[databaseName] NVARCHAR(255)
	, [tableName] NVARCHAR(255)
	, [columnName] NVARCHAR(255)
	, [Id] NVARCHAR(255)
	, [value] NVARCHAR(MAX)
	)

-- SeachList
IF object_Id('tempDb..#searchList') IS NOT NULL DROP TABLE #searchList
CREATE TABLE #searchList (
	[TABLE_CATALOG] NVARCHAR(255)
	, [TABLE_SCHEMA] NVARCHAR(255)
	, [TABLE_NAME] NVARCHAR(255)
	, [COLUMN_NAME] NVARCHAR(255)
	, [COLLATION_NAME] NVARCHAR(255)
	, [DATA_TYPE] NVARCHAR(255)
	, [ID_FIELD] BIT DEFAULT 0
	)


-- Variables
DECLARE @sql NVARCHAR(MAX)
DECLARE @database VARCHAR(255)
DECLARE @tableName VARCHAR(255)
DECLARE @columnName VARCHAR(255)
DECLARE @schema VARCHAR(255)
DECLARE @dataType VARCHAR(255)
DECLARE @collation VARCHAR(255)
DECLARE @idField BIT
DECLARE @msgText VARCHAR(300)
DECLARE @now DATETIME


-- Populate the SearchList
DECLARE databaseCursor CURSOR LOCAL READ_ONLY FOR SELECT
	[name]
FROM
	sys.databases
WHERE
	[name] LIKE @dbMask

OPEN databaseCursor

	FETCH NEXT FROM databaseCursor INTO @database

	WHILE ( @@FETCH_STATUS = 0 ) BEGIN

		SET @msgText = 'Scanning ' + QUOTENAME(@database) + ' for target columns'
		RAISERROR(@msgText, 0, 1) WITH NOWAIT

		SET @sql = N'
		INSERT INTO #searchList (
			[TABLE_CATALOG]
			, [TABLE_SCHEMA]
			, [TABLE_NAME]
			, [COLUMN_NAME]
			, [COLLATION_NAME]
			, [DATA_TYPE]
			)
		SELECT
			QUOTENAME([TABLE_CATALOG])
			, QUOTENAME([TABLE_SCHEMA])
			, QUOTENAME([TABLE_NAME])
			, QUOTENAME([COLUMN_NAME])
			, [COLLATION_NAME]
			, [DATA_TYPE]
		FROM
			' + QUOTENAME(@database) + '.information_schema.columns ic
		WHERE
			ic.[DATA_TYPE] IN (
				''VARCHAR''
				, ''TEXT''
				, ''CHAR''
				, ''NVARCHAR''
				, ''NCHAR''
				, ''NTEXT''
			)

			AND (
				ic.[TABLE_NAME] NOT LIKE ''[_]%''
				OR @includeUnderscoreTables = 1
				)

			AND NOT EXISTS (
				SELECT 1
				FROM
					#ignore i
				WHERE
					i.[TABLE_NAME] = ic.[TABLE_NAME] COLLATE DATABASE_DEFAULT
				)

		UPDATE sl
		SET
			[ID_FIELD] = 1
		FROM
			#searchList sl
			JOIN ' + QUOTENAME(@database) + '.information_schema.columns isc ON
				QUOTENAME(isc.[TABLE_CATALOG]) = sl.[TABLE_CATALOG] COLLATE DATABASE_DEFAULT
				AND QUOTENAME(isc.[TABLE_SCHEMA]) = sl.[TABLE_SCHEMA] COLLATE DATABASE_DEFAULT
				AND QUOTENAME(isc.[TABLE_NAME]) = sl.[TABLE_NAME] COLLATE DATABASE_DEFAULT
				AND isc.[COLUMN_NAME] = ''Id'' COLLATE DATABASE_DEFAULT'

		IF @debug = 1 PRINT @sql

		EXEC sp_ExecuteSql
			@sql
			, N'@includeUnderscoreTables BIT
			, @database VARCHAR(255)'
			, @includeUNderscoreTables
			, @database

		FETCH NEXT FROM databaseCursor INTO @database
	END
CLOSE databaseCursor
DEALLOCATE databaseCursor

RAISERROR('', 0, 1) WITH NOWAIT

SET @msgText = 'Starting Search for string : ' + REPLACE(@searchFor, '%', '*')
RAISERROR(@msgText, 0, 1) WITH NOWAIT

RAISERROR('', 0, 1) WITH NOWAIT

-- Now serach through the list. This can take a while!
DECLARE searchCursor CURSOR LOCAL READ_ONLY FOR
SELECT
	[TABLE_CATALOG]
	, [TABLE_SCHEMA]
	, [TABLE_NAME]
	, [COLUMN_NAME]
	, [DATA_TYPE]
	, [COLLATION_NAME]
	, [ID_FIELD]
FROM
	#searchList
ORDER BY
	[TABLE_CATALOG]
	, [TABLE_SCHEMA]
	, [TABLE_NAME]
	, [COLUMN_NAME]

OPEN searchCursor

	FETCH NEXT FROM searchCursor INTO
		@database
		, @schema
		, @tableName
		, @columnName
		, @dataType
		, @collation
		, @IdField

	WHILE ( @@FETCH_STATUS = 0 ) BEGIN

		SET @now = GETDATE()
		SET @msgText =
			CONVERT(CHAR(11), @now, 106) + ' @ '
			+ CONVERT(CHAR(8), @now, 108)
			+ ' - Checking.... '
			+ @database +
			'.'
			+ @schema
			+
			'.'
			+ @tableName
			+ '.'
			+ @columnName

		RAISERROR(@msgText, 0 , 1) WITH NOWAIT
			
		SET @sql = N'
		INSERT INTO #found (
			[databaseName]
			, [tableName]
			, [columnName]
			, [Id]
			, [value]
			)
		SELECT
			@database
			, @tableName
			, @columnName
			, ' + CASE @IdField WHEN 1 THEN '[Id]' ELSE 'NULL' END + '
			, ' + @columnName + '
		FROM
			' + @database + '.' + @schema + '.' + @tableName + ' WITH (NOLOCK)
		WHERE
			' + @columnName + ' LIKE @searchFor'

		-- Show and execute the sql
		IF @debug = 1 PRINT @sql

		EXEC sp_executeSql
			@sql
			, N'@database NVARCHAR(255)
			, @tableName NVARCHAR(255)
			, @columnName NVARCHAR(255)
			, @searchFor NVARCHAR(255)'
			, @database
			, @tableName
			, @columnName
			, @searchFor

		FETCH NEXT FROM searchCursor INTO
			@database
			, @schema
			, @tableName
			, @columnName
			, @dataType
			, @collation
			, @IdField

	END

CLOSE searchCursor
DEALLOCATE searchCursor

SELECT * FROM #found

And it returns this result set


databaseName	tableName	columnName	Id	value
[ReFlex_Main]	[Component]	[description]	899	Panel containing the footer navigation
[ReFlex_Main]	[Component]	[implementingClass]	899	com.vebnet.reflex.web.core.panel.navigation.FooterNavigationPanel
[ReFlex_Main]	[Component]	[name]	899	Footer Navigation
[ReFlex_Main]	[ImporterBespokeConfig]	[className]	6	com.vebnet.reflex.domain.importer.bespoke.validator.DefaultFooterRowValidator
[ReFlex_Main]	[ImporterTargetField]	[name]	68	Footer
[ReFlex_Main]	[Layout]	[name]	1066	Admin Footer
[ReFlex_Main]	[Layout]	[name]	1065	Employee Footer
[ReFlex_Main]	[Navigation]	[name]	5	Admin Footer
[ReFlex_Main]	[Navigation]	[name]	4	Employee Footer
[ReFlex_Main]	[Navigation]	[navigationType]	4	Footer
[ReFlex_Main]	[Navigation]	[navigationType]	5	Footer

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Jim Beam
Posting Yak Master

137 Posts

Posted - 2010-09-23 : 10:04:28

Who da man?

YOU DA MAN!!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-09-23 : 10:10:01

I take it it worked then.

Glad to help.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-23 : 12:42:04

Here's another solution: http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Elatesummer
Starting Member

2 Posts

Posted - 2011-09-13 : 11:29:22

As much as I hate to resurrect old threads, I'm trying to do something very similar. I'm actually using code which seems identical to the one linked by tkizer although I think I found it elsewhere a while back...

I have the misfortune to be working with a rather large database which enjoys such column types as 'ntext' - a lot - and the creators appear to have held a deep distrust of such things as foreign keys or using integers as a primary key (most of the primary keys are varchar. Sadly this is not a joke...) so I have ended up performing a few of these full database searches for a text string in order to create some form of data model. Even more unfortunately, I can't simply add foreign keys as I find them as it's a third party product.

Anyway.

The code in the last link won't search ntext, probably because in the final begin...end statement it uses a LEFT function, which isn't a fan of ntext. However, due to the issues I've explained, I need to be able to include ntext and text fields in the search for a text string.

Can anyone help?

I've tried (very simplistically!) the below:

IF @ColumnName IS NOT NULL
BEGIN
SET @colConv = CONVERT(VARCHAR(200),@ColumnName)
INSERT INTO #RAtmpResults1 (ColumnName, ColumnValue)
EXEC ('SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT('
+ @colConv
+ ', 3630) FROM ' + @TableName + ' (NOLOCK) '
+ ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2)
END

I still get the good old "Argument data type ntext is invalid for argument 1 of left function" error message - does anyone have a neat idea to get around this?

Thanking you in advance........

Elatesummer
Starting Member

2 Posts

Posted - 2011-09-15 : 08:03:09

Answered my own query...I rewrote the code. It won't search views or stored procedures any more, but if anyone's interested my revised version is below...Feel free to pick it to shreds :)

DECLARE @str varchar(50), @int int, @valType nvarchar(6), @search varchar(50), @partial varchar(10), @inctext binary, @ID int

----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------

SET @str = '0000613'
--> THE ABOVE IS USED TO ENTER A STRING SEARCH VALUE - CAN BE THE FULL OR PARTIAL STRING
SET @int = 427
--> THE ABOVE IS USED TO ENTER AN INTEGER SEARCH VALUE - MUST BE THE FULL INTEGER
SET @valType = 'string'
--> THE ABOVE IS USED TO INDICATE WHAT TYPE OF VALUE YOU ARE SEARCHING FOR - CHOOSE ONE OF DATE, STRING, INT.
SET @partial = 'full'
--> THE ABOVE IS USED TO INDICATE WHETHER A STRING SEARCH IS A PARTIAL OR FULL SEARCH ITEM
SET @inctext = 1
--> THE ABOVE IS USED TO INDICATE WHETHER YOU WISH TO INCLUDE TEXT AND NTEXT FIELDS.
-- THIS MAKES THE SEARCH MUCH SLOWER BUT MAY BE REQUIRED
-- THIS ONLY APPLIES TO STRING SEARCHES

----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
-- --
-- PLEASE DO NOT CHANGE ANY OF THE VARIABLES ENTERED BELOW THIS POINT --
-- --
----------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------
BEGIN

IF UPPER(@valType) = 'INT'
AND @int = NULL
SELECT 'You have selected an integer search without entering an integer value' AS [ERROR IN SEARCH TERMS]

IF UPPER(@valType) = 'STRING'
AND @str = ''
SELECT 'You have selected a string search without entering a string value' AS [ERROR IN SEARCH TERMS]

IF UPPER(@valType) = 'DATE'
AND @str = ''
SELECT 'You have selected a date search without entering a date into the string value' AS [ERROR IN SEARCH TERMS]

CREATE TABLE #raSearchTypes (dataType varchar(50))

CREATE TABLE #raTMPsearchColumns
(ID INT IDENTITY(1,1) NOT NULL
, TABLE_SCHEMA varchar(50)
, TABLE_NAME varchar(250)
, COLUMN_NAME varchar(250)
, DATA_TYPE varchar(50))

IF UPPER(@valType) = 'INT'
AND @int IS NOT NULL
BEGIN
INSERT INTO #raSearchTypes (dataType)
SELECT 'int' UNION
SELECT 'bigint'
END

IF UPPER(@valType) = 'DATE'
AND @str != ''
BEGIN
INSERT INTO #raSearchTypes (dataType)
SELECT 'datetime' UNION
SELECT 'smalldatetime'
END

IF UPPER(@valType) = 'STRING'
AND @str != ''
BEGIN
INSERT INTO #raSearchTypes (dataType)
SELECT 'text' UNION
SELECT 'ntext' UNION
SELECT 'varchar' UNION
SELECT 'nvarchar' UNION
SELECT 'char' UNION
SELECT 'nchar'

SET @search = CASE WHEN @partial = 'full'
THEN QUOTENAME(@str,'''')
WHEN @partial = 'partial'
THEN QUOTENAME('%' + @str + '%','''')
ELSE QUOTENAME('%' + @str + '%','''')
END
END

INSERT INTO #raTMPsearchColumns (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE)
SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN (SELECT dataType
FROM #raSearchTypes)

CREATE TABLE #raTMPResults
(ID int, FULL_VALUE varchar(8000) NULL)

SET @ID = (SELECT MIN(ID) FROM #raTMPsearchColumns)
WHILE @ID <= (SELECT MAX(ID) FROM #raTMPsearchColumns)
BEGIN
DECLARE @schema varchar(50), @table_name varchar(250), @column_name varchar(250), @data_type varchar(50)

SET @schema = (SELECT TABLE_SCHEMA FROM #raTMPsearchColumns WHERE ID = @ID)
SET @table_name = (SELECT TABLE_NAME FROM #raTMPsearchColumns WHERE ID = @ID)
SET @column_name = (SELECT COLUMN_NAME FROM #raTMPsearchColumns WHERE ID = @ID)
SET @data_type = (SELECT DATA_TYPE FROM #raTMPsearchColumns WHERE ID = @ID)

IF (SELECT [TYPE] FROM sys.objects WHERE [object_id] = OBJECT_ID(@schema + '.' + @table_name))
IN ('S', 'U', 'IT')
BEGIN
IF UPPER(@valType) = 'DATE'
EXEC (' INSERT INTO #raTMPResults (ID, FULL_VALUE)
SELECT ' + @ID + ', [' + @column_name + '] FROM [' + @schema + '].[' + @table_name
+ '] WHERE DATEADD(DD,0,DATEDIFF(DD,0,[' + @column_name + '])) = ' + @str)

IF UPPER(@valType) = 'STRING'
EXEC (' INSERT INTO #raTMPResults (ID, FULL_VALUE)
SELECT ' + @ID + ', [' + @column_name + '] FROM [' + @schema + '].[' + @table_name
+ '] WHERE [' + @column_name + '] LIKE ' + @search)

IF UPPER(@valType) = 'INT'
EXEC (' INSERT INTO #raTMPResults (ID, FULL_VALUE)
SELECT ' + @ID + ', [' + @column_name + '] FROM [' + @schema + '].[' + @table_name
+ '] WHERE [' + @column_name + '] = ' + @int)
END
SET @ID = (SELECT MIN(ID) FROM #raTMPsearchColumns WHERE ID > @ID)
END

SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.COLUMN_NAME, a.DATA_TYPE, b.FULL_VALUE AS COLUMN_VALUE
FROM #raTMPsearchColumns a
INNER JOIN #raTMPResults b
ON (a.ID = b.ID)

DROP TABLE #raTMPResults
DROP TABLE #raTMPsearchColumns
DROP TABLE #raSearchTypes

END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources