August 16, 2017 at 7:42 am
Is there a script to search a string stored in all cell of all tables?
For example, I want to find out MEMBER_FIRST_NAME='JOHN'.
August 16, 2017 at 7:50 am
So you only want to look in columns called MEMBER_FIRST_NAME? Or do you want to look in all columns? If it's just the one column name, query INFORMATION_SCHEMA.COLUMNS to find out what tables have that column, and write a separate query for each table returned. If it's all columns, it's just too tedious. Get Redgate's SQL Search or something similar to make your life much easier.
John
Edit - changed "like" to "life"
August 16, 2017 at 7:50 am
Take a look at SQL Down Under and their SDU Tools for FindStringWithinADatabase. But what you want is not fast and is a very dirty operation.
If "MEMBER_FIRST_NAME" is a column I would recommend looking through the sys.columns view and building a SQL statement that way instead.
August 16, 2017 at 8:09 am
Sorry, I did not describe clearly.
I want to search all columns, all cells in which store the string "John"
August 16, 2017 at 8:18 am
adonetok - Wednesday, August 16, 2017 8:09 AMSorry, I did not describe clearly.
I want to search all columns, all cells in which store the string "John"
Then you want something like the SDU Tool I already detailed.
I would question the need to do this as it wont be quick and is a very dirty thing to do.
August 16, 2017 at 8:36 am
If got a stored procedure I use for searching for a string in a database:IF NOT EXISTS(SELECT 1
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'INFSearchAllTables'
AND ROUTINE_TYPE = N'PROCEDURE')
BEGIN
EXEC ('CREATE PROCEDURE [dbo].[INFSearchAllTables] AS BEGIN')
END
GO
GO
-- **********************************************************************
-- Purpose: To search all columns of all tables for a given search string
-- Sample Usage: EXEC INFSearchAllTables 'nxec', 'Orders'
-- $Revision: 1.5 $
-- **********************************************************************
ALTER PROC INFSearchAllTables
(
@SearchStr nvarchar(100),
@ExcludeTablesList nvarchar(Max) = '', -- CSV List e.g.: 'myTable1,myTable2' of tables not to be included in search
@TableNotLike nvarchar(Max) = '', -- e.g.: 'tmp%'
@TableLike nvarchar(Max) = '%',
@Debug bit = 0 -- Whether to just output the selects without execution
)
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #Results
(
TableName nvarchar(370),
ColumnName nvarchar(370),
ColumnValue nvarchar(3630)
)
DECLARE @TableName nvarchar(256)
DECLARE @ColumnName nvarchar(128)
DECLARE @SearchStr2 nvarchar(110)
DECLARE @sSQL nvarchar(Max)
DECLARE @NewLine nvarchar(Max)
SET @NewLine = CHAR(13) + CHAR(10)
SET @TableName = ''
SET @SearchStr2 = QuoteName('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SELECT @TableName = 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
AND NOT EXISTS (SELECT 1
FROM dbo.chrSplitList(@ExcludeTablesList,',')
WHERE Value = TABLE_NAME)
AND TABLE_NAME LIKE @TableLike
AND TABLE_NAME NOT LIKE @TableNotLike
WHILE (@TableName IS NOT NULL)
AND (@ColumnName IS NOT NULL)
BEGIN
SELECT @ColumnName = 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')
AND QuoteName(COLUMN_NAME) > @ColumnName
IF @ColumnName IS NOT NULL
BEGIN
SET @sSQL = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)' + @NewLine +
' FROM ' + @TableName + ' WITH (NOLOCK)' + @NewLine +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 + @NewLine + @NewLine
IF @Debug = 0
BEGIN
INSERT INTO #Results
(
TableName,
ColumnName,
ColumnValue
)
EXEC (@sSQL)
END
ELSE
BEGIN
PRINT @sSQL
END
--END IF
END
--END IF
END
--END WHILE
END
--END WHILE
SELECT R.TableName,
R.ColumnName,
R.ColumnValue
FROM #Results R
ORDER BY R.ColumnName, R.ColumnValue
END
GO
August 16, 2017 at 9:04 am
Thank you for help.
Got an error
Invalid object name 'dbo.chrSplitList'
Is dbo.chrSplitList a function?
August 16, 2017 at 9:22 am
adonetok - Wednesday, August 16, 2017 9:04 AMThank you for help.
Got an error
Invalid object name 'dbo.chrSplitList'
Is dbo.chrSplitList a function?
-- Splits a string into rows
CREATE FUNCTION [dbo].[chrSplitList]
(
@list nvarchar(MAX),
@separator nvarchar(MAX) = ';'
)
RETURNS @table TABLE (Value nvarchar(4000))
AS BEGIN
DECLARE @position INT, @previous INT
SET @list = @list + @separator
SET @previous = 1
SET @position = CHARINDEX(@separator, @list)
WHILE @position > 0 BEGIN
IF @position - @previous > 0
INSERT INTO @table VALUES (SUBSTRING(@list, @previous, @position - @previous))
IF @position >= LEN(@list) BREAK
SET @previous = @position + LEN(@separator)
SET @position = CHARINDEX(@separator, @list, @previous)
END
RETURN
END
GO
August 16, 2017 at 9:52 pm
adonetok - Wednesday, August 16, 2017 7:42 AMIs there a script to search a string stored in all cell of all tables?
For example, I want to find out MEMBER_FIRST_NAME='JOHN'.
As an fyi, Red Gate has a free tool called SQL Search - or at least it used to be free - that will do all this and many more things for you. It is great for doing metadata research and is a plug in for SSMS.
August 17, 2017 at 8:10 am
Red-gate only search for object name not string stored in cell
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply