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
Michael FranzCFOAsked:
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.

Scott PletcherSenior DBACommented:
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

0
pcelbaCommented:
Wow. Much easier way also exists:
1) Create a script which will list all tables having UID column in your database:
select 'SELECT * FROM ' + TABLE_SCHEMA+'.'+TABLE_NAME + ' WHERE '+COLUMN_NAME+' = ''F125-000-5555-KL647''' 
from INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'UID'

Open in new window

If the column name is different from UID then you may list all tables having column with given data type:
select 'SELECT * FROM ' + TABLE_SCHEMA+'.'+TABLE_NAME + ' WHERE '+COLUMN_NAME+' = ''F125-000-5555-KL647''' 
from INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'char' AND CHARACTER_MAXIMUM_LENGTH = 19

Open in new window

2) Execute the script and look for the table(s) giving non empty results.

You may extend the script by IF condition so the output will list just tables of your interest. Just change the first line to:
select 'IF EXISTS (SELECT * FROM ' + TABLE_SCHEMA+'.'+TABLE_NAME + ' WHERE '+COLUMN_NAME+' = ''F125-000-5555-KL647'') PRINT ''' + TABLE_SCHEMA+'.'+TABLE_NAME + ''''

Open in new window

Output from the last query can be following (the generated script has just one line in my case):
IF EXISTS (SELECT * FROM dbo.tstguid WHERE sometext = 'F125-000-5555-KL647') PRINT 'dbo.tstguid'

As a starting query you may simply execute:

SELECT * from INFORMATION_SCHEMA.COLUMNS

and you'll see all values which are used to build the script.
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
Newbi22, do you still need help with this question?
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.