Find all tables in SQL2000, that contain a specific GUID

book

Article ID: 158423

calendar_today

Updated On:

Products

Management Platform (Formerly known as Notification Server)

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").

Resolution

 

declare @searchvalue uniqueidentifier

set @searchValue = '{"Enter Search Guid here"}'  --Enter your the GUID here

 

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

 

Applies To

 

Product Version 6.0
Platform Windows Server 2003
Platform Version Standard Server
SQl Server 2000