Author: M Jarvis
Posted: 2019-07-26 15:20:46 Link
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
---
_______________________________________________
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/CAPT54raXbg1XA-4AD+dGfasY+VArHyPU6vZ5YojiG0fx=HuwuA@mail.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.
Author: Frank Cazabon
Posted: 2019-07-29 07:57:29 Link
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.