SQL Query to Search for GUID and their Corresponding Tables

Products

• IT Management Suite

Issue/Introduction

The following SQL script will traverse an Altiris database and find all tables in which a specified GUID appears. It reports both the table and the column. The only requirement is to change the value of the guid being searched (Under the line that reads "Enter Search Guid here").

Environment

ITMS 8.x

Resolution

In SQL Management Studio please run the following SQL Query.  Make sure to enter the GUID you are searching for where indicated in the query.

declare @searchvalue uniqueidentifier

--Change the GUID below.  Example:  {AA7E70B7-6C1E-4DA0-8D32-0097E45B02A9}

set @searchValue = '{Enter Your GUID Here}'  --Enter your the GUID here

--Do Not Change Anything Below This Linke

if object_id('tempdb..#results') is not null drop table #results

create table #results (TableSchema sysname, TableName sysname) 

declare @sql nvarchar(4000) 

declare @cursor1 cursor

declare @tablename sysname

declare @tableschema sysname

declare @cursor2 cursor

declare @columnname sysname

declare @searchFields nvarchar(4000) 

set @cursor1 = cursor for

  select t.TABLE_SCHEMA, t.TABLE_NAME

  from INFORMATION_SCHEMA.Tables t

  where t.TABLE_TYPE = 'BASE TABLE'

    and exists (

      select * from INFORMATION_SCHEMA.Columns c

      where c.TABLE_NAME = t.TABLE_NAME

        and c.TABLE_SCHEMA = t.TABLE_SCHEMA

        and c.DATA_TYPE = 'uniqueidentifier'

      ) 

open @cursor1

while 1=1 begin

  fetch next from @cursor1 into @tableschema, @tablename

  if @@fetch_status <> 0 break 

  set @searchFields = ''

  set @cursor2 = cursor for

    select c.COLUMN_NAME

    from INFORMATION_SCHEMA.Columns c

    where c.TABLE_NAME = @tablename

      and c.TABLE_SCHEMA = @tableschema

      and c.DATA_TYPE = 'uniqueidentifier' 

  open @cursor2

  while 1=1 begin

    fetch next from @cursor2 into @columnname

    if @@fetch_status <> 0 break

    set @searchFields = @searchFields + ', ' + quotename(@columnname)

  end      

  set @searchFields = substring(@searchFields,3,len(@searchFields))

  set @sql = ' insert #results'

           + ' select '''+@tableschema+''','''+@tablename+''''

           + ' from '+quotename(@tableschema)+'.'+quotename(@tablename)

           + ' where @searchValue in ('+@searchFields+')' 

  print @sql

  exec sp_executesql @sql, N'@searchValue uniqueidentifier', @searchValue

end

select * from #results