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

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 ?

Hi Vikas, looks promising ,  But Do you have another  object by Name CommaSeparatedToTable ?

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

Select allOpen in new window

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

https://www.experts-exchange.com/questions/28116357/SQL-Query-to-look-for-a-name-in-all-DB's-and-Tables.html?anchorAnswerId=39133720#a39133720
https://www.experts-exchange.com/questions/28084334/Looking-for-a-field-within-an-entire-Database.html?anchorAnswerId=ccepted-solution#accepted-solution
https://www.experts-exchange.com/questions/28073347/Search-for-particular-value-through-out-the-database-user-defined-tables.html?anchorAnswerId=39012055#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.

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'

Select allOpen in new window

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 ?