Sql Server Search a given value in Given Column names of Given Tables in Given Schema

Hi Experts,

       I was looking for  a generic Procedure in Sql Server  

        I need to search for  
            a given value in
                 a given list of Columns  in  
                    a given List of Tables in
                          a Given Schema

         For example  ( Just a fictitious example)  
                SearchValue = 'TestValue'
                 SearchColumns = 'ID, Name, Description'
                 SearchTables = 'TB_Category,TB_Address'
                 
           (The Table Names or Column names can be kept as List  or whatever convenient )
   
        Thanks

              Sam
Sam OZAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:
It's do-able, but you'd have to write something using Dynamic SQL.  Do you have to run this repeatedly, or would a GUI tool like Apex Search do the trick?
0
Sam OZAuthor Commented:
Hi, Thanks for the tool Apex Search  . It is an awesome tool  ( But unfortunately   not free)
   
  One of my requirement is - I need to input the column names also (    The Tool has only  ColumnTypes)

  I have to use this frequently , I can create a Stored Proc and keep it   ( I already have some Procedure to look for all Columns in all Tables . But it returns many redundant information. That is why I am looking for a filter )

 Can someone please provide me with a  Stored Procedure for this ?
0
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

This Query will help you for the same

DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_id       INT,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000),
	@TABLES VARCHAR(100) = 'DimCalendar,DimDate',
	@SCHEMA VARCHAR(100) = 'DBO,SYS',
	@COLUMNS VARCHAR(100) = 'MONTH,DATE'

SET @search_string = '1'

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U' and name in (SELECT * FROM [dbo].[CommaSeparatedToTable](@TABLES,','))
AND schema_id IN (SELECT schema_id FROM SYS.schemas WHERE NAME IN (SELECT * FROM [dbo].[CommaSeparatedToTable](@SCHEMA,',')))

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id
	and name in (SELECT * FROM [dbo].[CommaSeparatedToTable](@COLUMNS,','))

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + '] LIKE ''%' + @search_string + '%'') PRINT ''' + @table_name + ', ' + @column_name + ''''

        EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
    END

    CLOSE columns_cur

    DEALLOCATE columns_cur

    FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur

DEALLOCATE tables_cur

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Sam OZAuthor Commented:
Hi Vikas, looks promising ,  But Do you have another  object by Name CommaSeparatedToTable ?
0
Vikas GargBusiness Intelligence DeveloperCommented:
CREATE FUNCTION [dbo].[CommaSeparatedToTable] ( @StringInput VARCHAR(8000), @Delimiter nvarchar(1))
RETURNS @OutputTable TABLE ( [String] VARCHAR(50) )
AS
BEGIN

    DECLARE @String    VARCHAR(50)

    WHILE LEN(@StringInput) > 0
    BEGIN
        SET @String      = LEFT(@StringInput, 
                                ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput) - 1, -1),
                                LEN(@StringInput)))
        SET @StringInput = SUBSTRING(@StringInput,
                                     ISNULL(NULLIF(CHARINDEX(@Delimiter, @StringInput), 0),
                                     LEN(@StringInput)) + 1, LEN(@StringInput))

        INSERT INTO @OutputTable ( [String] )
        VALUES ( @String )
    END

    RETURN
END

Open in new window

0
PortletPaulfreelancerCommented:
There is a script by Scott Pletcher that may suit your needs. It's been used a few times but the explanations differ a little so I've listed 3

http://www.experts-exchange.com/Database/MS-SQL-Server/SQL_Server_2008/Q_28116357.html#a39133720
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28084334.html#accepted-solution
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28073347.html#a39012055

The basic difference to most approaches is that this script does not re-scan each table for each field. This may be particularly useful as you are stipulating the fields to scan for anyway.
0
Scott PletcherSenior DBACommented:
Here's a fuller version that has more of the capabilities you're asking for.  Except it does not have schema selection capability; however, it uses "sys.tables" as the source for the table names, which automatically excludes system tables.

Temp tables are used to specify the search criteria.  You can create and the load the temp tables before calling the proc.  If one/more criteria tables don't exist, the proc will create them and load them with the default value(s) specified in the proc.  "Exclude" works like "DENY", i.e., it overrides what would otherwise be included.  NOTE: Columns of data type "sql_variant" are never searched.

The search results are loaded into a temp table so that they can be sorted and formatted for output.  Naturally you might prefer to change the results table to be permanent in some cases.

For maximum flexibility, the proc is created in the master db and marked as system, so that it can be used from any db.

If you have any qs, please ask.


USE master
GO
DROP PROCEDURE dbo.sp_Find_Column_Value_In_Selected_Tables
GO
CREATE PROCEDURE dbo.sp_Find_Column_Value_In_Selected_Tables
    @value_to_match varchar(50), --single value or LIKE pattern; if % is present, @value_comparison is forced to "LIKE".
    @value_comparison varchar(4) = '=', --valid operator: '=', '<>', 'LIKE', etc.
    @print_sql bit = 0 --1=print sql used to search for values; 0=don't.    
AS
SET NOCOUNT ON;
--EXEC any_db_name.dbo.sp_Find_Column_Value_In_Selected_Tables '1454497812', '=', 1

IF @value_comparison IS NULL
    SET @value_comparison = '='
IF @print_sql IS NULL
    SET @print_sql = 0
IF @value_to_match LIKE '%[%]%' AND @value_comparison <> 'LIKE'
    SET @value_comparison = 'LIKE'

IF OBJECT_ID('tempdb.dbo.#columns_to_include_or_exclude') IS NULL
BEGIN
    CREATE TABLE #columns_to_include_or_exclude (
        column_name_pattern nvarchar(128) PRIMARY KEY, --can be individual name or LIKE pattern, must match sys.columns.name!
        include_or_exclude char(1) NOT NULL DEFAULT 'E' CHECK(include_or_exclude IN ('E', 'I'))
        )
    INSERT INTO #columns_to_include_or_exclude VALUES('Column_Name_1', 'I') --'%scac%'
END --IF
IF OBJECT_ID('tempdb.dbo.#data_types_to_include_or_exclude') IS NULL
BEGIN
    CREATE TABLE #data_types_to_include_or_exclude (
        data_type_name_pattern nvarchar(128) PRIMARY KEY, --can be individual name or LIKE pattern, must match sys.types.name!
        include_or_exclude char(1) NOT NULL DEFAULT 'E' CHECK(include_or_exclude IN ('E', 'I'))
        )
    --INSERT INTO #data_types_to_include_or_exclude VALUES('sql_variant', 'E')
    --INSERT INTO #data_types_to_include_or_exclude VALUES('uniqueidentifier', 'E')
    INSERT INTO #data_types_to_include_or_exclude VALUES('%char%', 'I')
    --INSERT INTO #data_types_to_include_or_exclude VALUES('%int%', 'I')
END --IF
IF OBJECT_ID('tempdb.dbo.#tables_to_include_or_exclude') IS NULL
BEGIN
    CREATE TABLE #tables_to_include_or_exclude (
        table_name_pattern nvarchar(128) PRIMARY KEY, --can be individual name or LIKE pattern, must match sys.tables.name!
        include_or_exclude char(1) NOT NULL DEFAULT 'E' CHECK(include_or_exclude IN ('E', 'I'))
        )
    INSERT INTO #tables_to_include_or_exclude VALUES('%', 'I')
    /*
    INSERT INTO #tables_to_include_or_exclude VALUES('%audit%', 'E')
    INSERT INTO #tables_to_include_or_exclude VALUES('%log', 'E')
    INSERT INTO #tables_to_include_or_exclude VALUES('sys%', 'E')
    INSERT INTO #tables_to_include_or_exclude VALUES('temp%', 'E')
    INSERT INTO #tables_to_include_or_exclude VALUES('tmp%', 'E')
    INSERT INTO #tables_to_include_or_exclude VALUES('%work%', 'E')
    */
END --IF
------------------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#find_string_in_tables') IS NULL
BEGIN
    CREATE TABLE #find_string_in_tables (
        schema_id int NOT NULL,
        object_id int NOT NULL,
        column_id int NOT NULL,
        column_name nvarchar(128) NOT NULL,
        column_type nvarchar(128) NULL,
        column_length int NULL,
        match_condition varchar(60) NULL,
        column_sql_comparison nvarchar(4000) NULL,
        column_sql_update nvarchar(4000) NULL,
        number_rows_that_matched_condition int NULL,
        PRIMARY KEY ( schema_id, object_id, column_name )
        )
END --IF
ELSE
BEGIN
    TRUNCATE TABLE #find_string_in_tables
END --ELSE

------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID('tempdb..#find_results') IS NOT NULL
    DROP TABLE #find_results

DECLARE @schema_id int
DECLARE @object_id int
DECLARE @column_id int
DECLARE @sql_template_column_comparison nvarchar(4000)
DECLARE @sql_template_update_result nvarchar(4000)
DECLARE @sql_template_update nvarchar(4000)
DECLARE @column_max int
DECLARE @sql nvarchar(max)
DECLARE @sql2 nvarchar(max)

SET @sql_template_column_comparison = ',SUM(CASE WHEN [$column$] $comparison$ ''$value$'' THEN 1 ELSE 0 END) AS [$column$_Count]' +
    CHAR(13) + CHAR(10)
SET @sql_template_update = '
UPDATE #find_string_in_tables SET number_rows_that_matched_condition = [$column$_Count] FROM #find_results 
WHERE schema_id = $schema_id$ AND object_id = $object_id$ AND column_name = ''$column$''' + CHAR(13) + CHAR(10)
--PRINT @sql_template_column_comparison

INSERT INTO #find_string_in_tables ( 
    schema_id, object_id, column_id, 
    column_name, column_type, column_length,
    match_condition, 
    column_sql_comparison, column_sql_update )
SELECT DISTINCT
    t.schema_id, c.object_id, c.column_id, 
    c.name, ty.name, c.max_length,
    @value_comparison + ' ''' + @value_to_match + '''',
    REPLACE(REPLACE(REPLACE(REPLACE(@sql_template_column_comparison, 
        '$column$', c.name),
        '$comparison$', @value_comparison),
        '$value$', @value_to_match), 
        '$column_id$', CAST(c.column_id AS varchar(10))),
    REPLACE(REPLACE(REPLACE(@sql_template_update, 
        '$column$', c.name),
        '$schema_id$', CAST(t.schema_id AS varchar(10))),
        '$object_id$', CAST(c.object_id AS varchar(10)))
FROM sys.tables t
INNER JOIN sys.columns c ON
    c.object_id = t.object_id
INNER JOIN sys.types ty ON
    c.system_type_id = ty.system_type_id AND
    c.user_type_id = ty.user_type_id
WHERE
    t.is_ms_shipped = 0 AND
    ((c.max_length = -1 OR c.max_length >= LEN(REPLACE(@value_to_match, '%', ''))) AND c.max_length <> 8016) AND    
    NOT EXISTS(
        SELECT 1
        FROM #columns_to_include_or_exclude ctie
        WHERE
            c.name LIKE ctie.column_name_pattern AND
            ctie.include_or_exclude = 'E'
    ) AND
    NOT EXISTS(
        SELECT 1
        FROM #data_types_to_include_or_exclude dttie
        WHERE
            ty.name LIKE dttie.data_type_name_pattern AND
            dttie.include_or_exclude = 'E'
    ) AND
    NOT EXISTS (
        SELECT 1
        FROM #tables_to_include_or_exclude ttie
        WHERE
            t.name LIKE ttie.table_name_pattern AND
            ttie.include_or_exclude = 'E'
    ) AND
    1 = CASE WHEN EXISTS(SELECT 1 FROM #columns_to_include_or_exclude WHERE include_or_exclude = 'I')
        THEN CASE WHEN EXISTS(
            SELECT 1 
            FROM #columns_to_include_or_exclude ctie 
            WHERE 
                c.name LIKE ctie.column_name_pattern AND
                ctie.include_or_exclude = 'I'
            ) THEN 1 ELSE 0 END ELSE 1 END AND
    1 = CASE WHEN EXISTS(SELECT 1 FROM #data_types_to_include_or_exclude WHERE include_or_exclude = 'I')
        THEN CASE WHEN EXISTS(
            SELECT 1 
            FROM #data_types_to_include_or_exclude dttie 
            WHERE 
                ty.name LIKE dttie.data_type_name_pattern AND
                dttie.include_or_exclude = 'I'
            ) THEN 1 ELSE 0 END ELSE 1 END AND
    1 = CASE WHEN EXISTS(SELECT 1 FROM #tables_to_include_or_exclude WHERE include_or_exclude = 'I')
        THEN CASE WHEN EXISTS(
            SELECT 1 
            FROM #tables_to_include_or_exclude ttie 
            WHERE
                OBJECT_NAME(c.object_id) LIKE ttie.table_name_pattern AND
                ttie.include_or_exclude = 'I'
            ) THEN 1 ELSE 0 END ELSE 1 END
ORDER BY
    t.schema_id, c.object_id, c.name

SELECT @column_max = MAX(column_count)
FROM (
    SELECT COUNT(*) AS column_count
    FROM #find_string_in_tables
    GROUP BY object_id
) AS derived

--PRINT @column_max
--SELECT * FROM #find_string_in_tables

DECLARE csrTable CURSOR FAST_FORWARD FOR
SELECT DISTINCT schema_id, object_id
FROM #find_string_in_tables
ORDER BY object_id

OPEN csrTable

SET NOCOUNT ON

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM csrTable INTO @schema_id, @object_id
    IF @@FETCH_STATUS <> 0
        BREAK
    SET @sql = ''
    SELECT @sql = @sql + column_sql_comparison 
    FROM #find_string_in_tables
    WHERE
        object_id = @object_id
    ORDER BY
        column_id
    SET @sql = CHAR(13) + CHAR(10) +
        'SELECT ''[' + SCHEMA_NAME(@schema_id) + '].' +
        '[' + OBJECT_NAME(@object_id) + ']'' AS Table_Name, ' + CHAR(13) + CHAR(10) +
        SUBSTRING(@sql, 2, LEN(@sql)) + --CHAR(13) + CHAR(10) already at end of @sql
        'INTO #find_results ' + CHAR(13) + CHAR(10) +
        'FROM [' + SCHEMA_NAME(@schema_id) + '].[' +OBJECT_NAME(@object_id) + '] WITH (NOLOCK) ' + CHAR(13) + CHAR(10)
    SET @sql2 = ''
    SELECT @sql2 = @sql2 + column_sql_update
    FROM #find_string_in_tables
    WHERE
        object_id = @object_id
    ORDER BY
        column_id
    SET @sql = @sql + @sql2
    IF @print_sql > 0
        PRINT @sql
    EXEC(@sql)
END --WHILE

DEALLOCATE csrTable

SET NOCOUNT OFF

SELECT
    DB_NAME() AS Database_Name,
    SCHEMA_NAME(fsit.schema_id) + '.' + OBJECT_NAME(fsit.object_id) AS Table_Name,
    fsit.Column_Name,
    fsit.Column_Type,
    fsit.Column_Length,
    fsit.Number_Rows_That_Matched_Condition,
    fsit.Match_Condition,
    'SELECT * ' + --DISTINCT [' + fsit.column_name + '] ' +
        'FROM [' + SCHEMA_NAME(fsit.schema_id) + '].[' + OBJECT_NAME(fsit.object_id) + '] ' +
        'WHERE [' + fsit.column_name + '] ' + fsit.Match_Condition + ';' AS Sql_To_Find_This_String_In_This_Table
FROM #find_string_in_tables fsit
--WHERE fsit.column_name LIKE '%s%v%c%'
ORDER BY
    number_rows_that_matched_condition DESC,
    Table_Name, Column_Name

GO

EXEC sp_MS_marksystemobject 'dbo.sp_Find_Column_Value_In_Selected_Tables'

Open in new window

0
Sam OZAuthor Commented:
Hi ScottPletcher
, The  your procedure is exactly what I was looking for ..But when I try to run it , I don't get any result .. Was it really tested ?
0
Scott PletcherSenior DBACommented:
Yes, it's been tested.

But, did you load the control values into the temp tables?  The proc needs to have the parameters of the search specified -- that's an inherent disadvantage of a generic search, you must specify criteria every time to make it specific to what you want for that run.  

See example below, based on the sample search values you specified in your initial q.  Or set the defaults within the proc itself to match your search.


--load values to control upcoming search
IF OBJECT_ID('tempdb.dbo.#columns_to_include_or_exclude') IS NOT NULL
    DROP TABLE #columns_to_include_or_exclude
IF OBJECT_ID('tempdb.dbo.#tables_to_include_or_exclude') IS NOT NULL
    DROP TABLE #tables_to_include_or_exclude

CREATE TABLE #columns_to_include_or_exclude (
    column_name_pattern nvarchar(128) PRIMARY KEY, --can be individual name or LIKE pattern, must match sys.columns.name!
    include_or_exclude char(1) NOT NULL DEFAULT 'E' CHECK(include_or_exclude IN ('E', 'I'))
    )
INSERT INTO #columns_to_include_or_exclude VALUES('ID', 'I')
INSERT INTO #columns_to_include_or_exclude VALUES('Name', 'I')
INSERT INTO #columns_to_include_or_exclude VALUES('Description', 'I')

CREATE TABLE #tables_to_include_or_exclude (
    table_name_pattern nvarchar(128) PRIMARY KEY, --can be individual name or LIKE pattern, must match sys.tables.name!
    include_or_exclude char(1) NOT NULL DEFAULT 'E' CHECK(include_or_exclude IN ('E', 'I'))
    )
INSERT INTO #tables_to_include_or_exclude VALUES('TB_Category', 'I')
INSERT INTO #tables_to_include_or_exclude VALUES('TB_Address', 'I')

EXEC dbo.sp_Find_Column_Value_In_Selected_Tables 'TestValue'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.