Link to home
Get AccessLog in
Avatar of Michael Franz
Michael Franz

asked on

Searching an entire SQL Database for a specific GUID

I am creating a report and I know need to join 2 tables, but I am not sure of the other table. I know the UID that I need to join to, but not sure what table it is in. I have tried looking in certain tables, but no luck. I need to search the entire database for this UID.

I am using Alteryx with a connection already established to the server and it have a sql editor to write the query in. The database name is "Chumley" and the UID is F125-000-5555-KL647
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Here's a generic procedure to find a given value in given:
tables and/or columns and/or with given data types.

I've adjusted the controlling temp tables to match your specific conditions for this search: NOTE: I assumed you only wanted to search %char% columns.  

First run all the code to create the proc.  Then run the code in the first comment block to set up this specific search and run it.  Naturally later you could modify the calling code to do other searches.

USE master;
/*
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.#data_types_to_include_or_exclude') IS NOT NULL DROP TABLE #data_types_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('%', 'I')
    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('%char%', '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('%', 'I')
EXEC Chumley.dbo.sp_Find_Column_Value_In_Selected_Tables 'F125-000-5555-KL647','=',1
*/
GO
IF EXISTS(SELECT 1 FROM master.sys.objects WHERE name = 'sp_Find_Column_Value_In_Selected_Tables')
    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.    
    @print_columns_to_search bit = 0,
    @include_ms_tables bit = 0
AS
SET NOCOUNT ON;
--EXEC any_db_name.dbo.sp_Find_Column_Value_In_Selected_Tables '1454497812', '=', 1, 0, 0

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('%', '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('%uniqueidentifier%', '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 )
--DECLARE @value_to_match varchar(50), @value_comparison varchar(10), @include_ms_tables bit SELECT @value_to_match = '2CD42194-17D9-4053-BC89-043ECB07BEFA', @value_comparison = '=', @include_ms_tables = 1
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   
    (@include_ms_tables = 1 OR t.is_ms_shipped = 0) AND
    ((c.max_length = -1 OR ty.name = 'uniqueidentifier' 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

IF @print_columns_to_search = 1
    SELECT * FROM #find_string_in_tables

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


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

ASKER CERTIFIED SOLUTION
Avatar of Pavel Celba
Pavel Celba
Flag of Czechia image

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Newbi22, do you still need help with this question?