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

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 (AT) gmail (DOT) com> wrote

foreign key refers [I think you mean that you have no REFERENCES
clause?]. I suppose the link [sic: reference] between these two tables
is purely managed programmatically. <<

I worked for a company that wrote crap like that when I first moved to
Austin. They are still in business, but down from ~1200 employees to
~200 and are now outsourcing their Indian operations to China as they
shrink.

This kind of thing falls apart in about a year. You get orphans all
over the schema (we choked a hard disk for a relatively small custom
sales commission package). There is no ways to validate or verify a
GUID; you need a proper key instead of a bad attempt at pointer
chains. The "pseudo Foreign Key" will be referenced by Cabbages and
Kings simply because all GUIDs can be compared (like pointers or other
exposed physical locators).

You really need to throw this thing out. But if you cannot, then
update your resume.

--CELKO-- wrote:

He can add REFERENCES clauses (once he figures out which ones should
be added, and cleans up any existing exceptions), surely?

Then he will have mimicked a 1970's pointer chain DB in SQL instead of
making this a properly designed RDBMS.

For example, if I use an VIN for an automobile, I can verify the VIN
by going to the automobile, the DMV, insurance company, etc. But if I
use a GUID (or any other hardware generated value), I have no trusted
external source for verification.

I do not have a good way to validate it, in fact. The magical
universal GUID might be used for an automobile, a squid or Britney
Spears!

--CELKO-- wrote:

Other than size, is this significantly different from any other type
of artificial record key, e.g. INT IDENTITY?

GUIDs are globally unique (we hope!) and IDENTITY is local to one
table in one schema on one machine. But neither of them is an
attribute of an entity in a data model, neither has validation and
verification, etc.

And I am glad that you called a **record key** since so many newbies
don't understand physical records versus logical rows. If you grew up
with a sequential file system, it is hard to lose that mindset.

Works perfectly!
Thanks a lot Dan !


On 22 sep, 16:35, "Dan Guzman" <guzma... (AT) nospam-online (DOT) sbcglobal.net>
wrote: