-- SQL search all columns in all tables for wildcard - search all tables in database
-- SQL search database for text - SQL Server search all tables in database for keyword
-- SQL search database for text - SQL Server search all tables in database for keyword
-- Search all text & xml columns of all tables for keyword - information_schema columns
-- Searching for text / data in SQL database without full-text search
-- Search all columns of all tables in a database for a keyword - list all table columns
USE AdventureWorks2008;
GO
-- T-SQL create stored procedure for searching all tables in database
CREATE PROC sprocSearchDatabaseForText
USE AdventureWorks2008;
GO
-- T-SQL create stored procedure for searching all tables in database
CREATE PROC sprocSearchDatabaseForText
@WildcardSearchTerm NVARCHAR(64)
AS
/* The following algorithm builds on metada from INFORMATION_SCHEMA views.
It loops through all tables in the database
by using an outer cursor WHILE loop.
Within the table cursor loop, it loops through all string/text/xml columns
by using an inner cursor WHILE loop.
It executes a dynamic SQL search for the wildcard keyword in each column
within the nested cursor loop.
*/
BEGIN
SET NOCOUNT ON
DECLARE @OutputLength VARCHAR(4) = '256', -- SQL Server 2008+ feature
@NolockOption CHAR(8) = '(NOLOCK))'
DECLARE @DynamicSQL NVARCHAR(MAX),
@SchemaTableName NVARCHAR(256),
@SchemaTableColumn NVARCHAR(128),
@SearchWildcard NVARCHAR(128)
SELECT @SearchWildcard = CHAR(39)+REPLACE(@WildcardSearchTerm,CHAR(39),
CHAR(39)+CHAR(39))+CHAR(39)
PRINT @SearchWildcard
DECLARE @SearchResults TABLE(
SchemaTableColumn NVARCHAR(384),
TextWithKeyword NVARCHAR(MAX)
)
DECLARE curAllTables CURSOR FOR
SELECT QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) AS SchemaTable
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') != 1
ORDER BY SchemaTable
OPEN curAllTables
FETCH NEXT FROM curAllTables
INTO @SchemaTableName
WHILE (@@FETCH_STATUS = 0) -- Outer cursor loop
BEGIN
PRINT @SchemaTableName
SET @SchemaTableColumn = ''
DECLARE curAllColumns CURSOR FOR -- Nested cursor
SELECT QUOTENAME(COLUMN_NAME)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = PARSENAME(@SchemaTableName,1)
AND TABLE_SCHEMA = PARSENAME(@SchemaTableName,2)
AND DATA_TYPE IN ('varchar','nvarchar','char','nchar','xml')
ORDER BY ORDINAL_POSITION
OPEN curAllColumns
FETCH NEXT FROM curAllColumns
INTO @SchemaTableColumn
WHILE (@@FETCH_STATUS = 0) -- Inner cursor loop (nested cursor while)
BEGIN
PRINT ' ' + @SchemaTableColumn
SET @DynamicSQL = 'SELECT ''' + @SchemaTableName + '.' +
@SchemaTableColumn + ''', LEFT(CONVERT(nvarchar(max),' +
@SchemaTableColumn + '),' + @OutputLength + ') FROM ' +
@SchemaTableName + ' ' +
@NolockOption + ' WHERE CONVERT(nvarchar(max),' +
@SchemaTableColumn + ') LIKE ' + @SearchWildcard
INSERT INTO @SearchResults
EXEC sp_executesql
@DynamicSQL
FETCH NEXT FROM curAllColumns
INTO @SchemaTableColumn
END -- Inner cursor loop
CLOSE curAllColumns
DEALLOCATE curAllColumns
FETCH NEXT FROM curAllTables
INTO @SchemaTableName
END -- Outer cursor loop
CLOSE curAllTables
DEALLOCATE curAllTables
SELECT DISTINCT SchemaTableColumn,
TextWithKeyword
FROM @SearchResults
END
GO
------------
-- Test searching entire database for keywords
EXEC sprocSearchDatabaseForText '%Hamilton%'
/* Partial results
SchemaTableColumn TextWithKeyWord
[Person].[Address].[AddressLine1] 9215 Hamilton Ave.
[Person].[Address].[AddressLine1] 9719 Hamilton Ave
[Person].[Address].[AddressLine1] 9732 Hamilton Ct.
[Person].[Address].[AddressLine1] 9738 Hamilton Drive
[Person].[Person].[LastName] Hamilton
*/
EXEC sprocSearchDatabaseForText 'Sánchez'
/* SchemaTableColumn TextWithKeyWord
[Person].[Person].[LastName] Sánchez
*/
EXEC sprocSearchDatabaseForText '%O''Donnell%'
/* SchemaTableColumn TextWithKeyWord
[Person].[Person].[LastName] O'Donnell
*/
-- XML column search example
EXEC sprocSearchDatabaseForText '%Programming%'
/*
SchemaTableColumn TextWithKeyWord
[HumanResources].[JobCandidate].[Resume] <ns:Resume xmlns:ns="http://schemas.microsoft.com/
sqlserver/2004/07/adventure-works/Resume"><ns:Name><ns:Name.Prefix>Mr.</ns:Name.Prefix><ns:Name.First>Krishna<
/ns:Name.First><ns:Name.Middle/><ns:Name.Last>Sunkammurali</ns:Name.Last><
ns:Name.Suffix/></ns:N
sqlserver/2004/07/adventure-works/Resume"><ns:Name><ns:Name.Prefix>Mr.</ns:Name.Prefix><ns:Name.First>Krishna<
/ns:Name.First><ns:Name.Middle/><ns:Name.Last>Sunkammurali</ns:Name.Last><
ns:Name.Suffix/></ns:N
*/
------------
Source : sqlusa
