Find a GUID (or anything) in ANY table in your database!
By CSharpner · July 16, 2008
Do you ever run across an ID, such as a GUID or an integer (or anything else, for that matter) while stepping through code and aren't quite sure which table it's supposed to be for or need to know where it's referenced anywhere in your database? I use GUIDs like they're going out of style for a plethora of reasons, especially in my tables. EVERYTHING in my tables has a GUID to identify it. I use them as primary keys and as foreign keys. They're computer generated, can be generated either on the db server, the web server, or even on the client and can be guaranteed to be unique in that table, or in an entire database, or in the entire universe, for that matter (try doing that with auto-incrementing integers!!!).
Anyway... I found myself needing to find an arbitrary GUID anywhere in the database and was doing a lot of manual querying. Finally, I decided that this could be EASILY automated by querying the database meta data for tables with GUID columns, then automatically querying those tables and columns for my GUID and output the results of each table and column the GUID was found in.
Below, is my solution: A stored procedure called FindGuidInAnyTable that takes a single parameter (a GUID) and searches the entire database for all occurrences of it. You could adapt this for any column type... not just GUIDs, BTW.
ALTER procedure .
(
@FindThisGUID uniqueidentifier
) as
begin
declare @TableSchema varchar(100)
declare @TableName varchar(100)
declare @NextColumn varchar(200)
declare @Select varchar(1000)
create table #ResultsTable
(
SchemaName varchar(100),
TableName varchar(100),
ColumnName varchar(100)
)
declare TableNameCursor cursor for
select
table_schema,
table_name
from
INFORMATION_SCHEMA.tables
where
table_type = 'BASE TABLE'
OPEN TableNameCursor
FETCH NEXT FROM TableNameCursor INTO @TableSchema, @TableName
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF (@@FETCH_STATUS <> -2) BEGIN
declare ColumnNameCursor cursor for
select distinct
COLUMN_NAME
from
INFORMATION_SCHEMA.COLUMNS
where
TABLE_SCHEMA = @TableSchema and
TABLE_NAME = @TableName and
DATA_TYPE = 'uniqueidentifier'
OPEN ColumnNameCursor
FETCH NEXT FROM ColumnNameCursor INTO @NextColumn
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF (@@FETCH_STATUS <> -2) BEGIN
set @select = 'insert into #ResultsTable select ''' + @TableSchema + ''' as TableSchema, ''' + @TableName + ''' as TableName, ''' + @NextColumn + ''' as ColumnName from [' + @TableSchema + '].[' + @TableName + '] where [' + @NextColumn + '] = ''' + cast(@FindThisGUID as varchar(50))+ ''''
print @select
exec(@select)
end
FETCH NEXT FROM ColumnNameCursor INTO @NextColumn
END
CLOSE ColumnNameCursor
DEALLOCATE ColumnNameCursor
end
FETCH NEXT FROM TableNameCursor INTO @TableSchema, @TableName
END
CLOSE TableNameCursor
DEALLOCATE TableNameCursor
select
count(*) as Instances,
SchemaName,
TableName,
ColumnName,
@FindThisGUID as GuidFound
from
#ResultsTable
group by
SchemaName,
TableName,
ColumnName
order by
SchemaName,
TableName,
ColumnName
end
For MS SQL Server versions prior to SQL Server 2005, do this:
CREATE procedure [dbo].[FindGuidInAnyTable]
(
@FindThisGUID uniqueidentifier
) as
begin
declare @TableName varchar(100)
declare @NextColumn varchar(200)
declare @Select varchar(1000)
create table #ResultsTable
(
TableName varchar(100),
ColumnName varchar(100)
)
declare TableNameCursor cursor for
select distinct
o.name
from
syscolumns c,
sysobjects o
where
c.id = o.id and
c.xtype = 36 and
o.xtype = 'U'
OPEN TableNameCursor
FETCH NEXT FROM TableNameCursor INTO @TableName
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF (@@FETCH_STATUS <> -2) BEGIN
declare ColumnNameCursor cursor for
select distinct
c.name
from
syscolumns c,
sysobjects o
where
c.id = o.id and
c.xtype = 36 and
o.name = @TableName and
o.xtype = 'U'
OPEN ColumnNameCursor
FETCH NEXT FROM ColumnNameCursor INTO @NextColumn
WHILE (@@FETCH_STATUS <> -1) BEGIN
IF (@@FETCH_STATUS <> -2) BEGIN
set @select = 'insert into #ResultsTable select ''' + @TableName + ''' as TableName, ''' + @NextColumn + ''' as ColumnName from [' + @TableName + '] where [' + @NextColumn + '] = ''' + cast(@FindThisGUID as varchar(50))+ ''''
print @select
exec(@select)
end
FETCH NEXT FROM ColumnNameCursor INTO @NextColumn
END
CLOSE ColumnNameCursor
DEALLOCATE ColumnNameCursor
end
FETCH NEXT FROM TableNameCursor INTO @TableName
END
CLOSE TableNameCursor
DEALLOCATE TableNameCursor
select
count(*) as Instances,
TableName,
ColumnName,
@FindThisGUID as GuidFound
from
#ResultsTable
group by
TableName,
ColumnName
order by
TableName,
ColumnName
end
Here's how you use it:
exec FindGuidInAnyTable '34fdbfa2-cdbd-4f34-bd2e-1423063fb707'
Here are what the results look like in my database:
I put this stored procedure in all of my databases. It's a real time saver!