Posted by Dan Guzman on 09/22/07 14:35

> Does anybody have a script or a tool that would allow me to specify a
> GUID and it would search through all the tables, detect which fields
> are GUIDS, find that value in a GUID field and report all tables and
> fieldnames where this value has been found?

Below is an sample script you can tweak for your needs.

IF OBJECT_ID(N'tempdb..#GuidColumns') IS NOT NULL
DROP TABLE #GuidColumns

DECLARE
@GUID uniqueidentifier,
@TABLE_SCHEMA sysname,
@TABLE_NAME sysname,
@COLUMN_NAME sysname,
@UpdateStatement nvarchar(4000)

-specify uniqueidentifier value to find
SET @GUID = '00000000-0000-0000-0000-000000000000'

SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
CAST(0 AS bit) AS Found
INTO #GuidColumns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE = 'uniqueidentifier'

DECLARE GuidColumns CURSOR
LOCAL FAST_FORWARD FOR
SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME
FROM #GuidColumns
OPEN GuidColumns
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GuidColumns INTO
@TABLE_SCHEMA,
@TABLE_NAME,
@COLUMN_NAME

IF @@FETCH_STATUS = -1 BREAK

SET @UpdateStatement =
N'UPDATE #GuidColumns SET Found = 1
WHERE
TABLE_SCHEMA = @TABLE_SCHEMA
AND TABLE_NAME = @TABLE_NAME
AND COLUMN_NAME = @COLUMN_NAME
AND EXISTS(
SELECT *
FROM ' + QUOTENAME(@TABLE_SCHEMA) + '.' +
QUOTENAME(@TABLE_NAME) +
N' WHERE ' + QUOTENAME(@COLUMN_NAME) + N' = @Guid)'

EXEC sp_executesql
@UpdateStatement,
N'@TABLE_SCHEMA sysname,
@TABLE_NAME sysname,
@COLUMN_NAME sysname,
@Guid uniqueidentifier',
@TABLE_SCHEMA = @TABLE_SCHEMA,
@TABLE_NAME = @TABLE_NAME,
@COLUMN_NAME = @COLUMN_NAME,
@Guid = @Guid

END
CLOSE GuidColumns
DEALLOCATE GuidColumns

SELECT
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
Found
FROM #GuidColumns
WHERE
Found = 1
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

<hugues.morel@gmail.com> wrote in message
news:1190453645.641117.185640@n39g2000hsh.googlegroups.com...
> Hi,
>
> I need to do a report from data in a database that was provided by a
> third party where there is no documentation at all. It contains more
> than hundred tables and each table has different GUID fields.
> In one table there is GUID as a primary key and another GUID as
> foreign key. But there is no relation defined to what table this
> foreign key refers. I suppose the link between these two tables is
> purely managed programatically.
>
> To find out to which table this foreign key is referring I would take
> a sample record, write down the value of this foreign key GUID and
> then search in all the tables of the database where this value also
> appears.
>
> Does anybody have a script or a tool that would allow me to specify a
> GUID and it would search through all the tables, detect which fields
> are GUIDS, find that value in a GUID field and report all tables and
> fieldnames where this value has been found?
>
> Thanks for any help.
> Hugues
>

[Back to original message]