Author: Frank Cazabon
Posted: 2019-07-29 at 07:57:29
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]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message: https://leafe.com/archives/byMID/4936e04d-bc00-47ef-8a01-a77ff6bcd72a@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.