Re: [NF] SQL - search all fields in all records in all tables

Or you could use the free SQL Search tool from red gate: https://www.red-gate.com/products/sql-development/sql-search/

Frank.

Frank Cazabon

On 26/07/2019 04:20 PM, M Jarvis wrote:

I didn't write this, nor do I know it's source. But some of our devs are treating it like a new toy so I thought I would pass it along in case someone could find it useful. Probably old old news to a lot of you.

One of our clients got a public records thing and wanted to search all fields in all tables for a particular string, in this case a name.

I'd done this in VFP but never had a need for it in SQL. No one here is saying it doesn't work but YMMV.

First two lines are the seed for the DB and the string to search for:

USE DATABASE_NAME DECLARE @SearchStr nvarchar(100) = 'SEARCH_TEXT' DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) SET @TableName = '' SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN SET @ColumnName = '' SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 )

 WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

 BEGIN
     SET @ColumnName =
     (
         SELECT MIN(QUOTENAME(COLUMN_NAME))
         FROM     INFORMATION_SCHEMA.COLUMNS
         WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
             AND    TABLE_NAME    = PARSENAME(@TableName, 1)
             AND    DATA_TYPE IN ('char', 'varchar', 'nchar',

'nvarchar', 'int', 'decimal') AND QUOTENAME(COLUMN_NAME) > @ColumnName )

     IF @ColumnName IS NOT NULL

     BEGIN
         INSERT INTO @Results
         EXEC
         (
             'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT('

• @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) END END

END

SELECT ColumnName, ColumnValue FROM @Results

--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html

---

[excessive quoting removed by server]