find unique identifier through multiple tables

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

Re: find unique identifier through multiple tables

> 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

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
>

Re: find unique identifier through multiple tables

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

Re: find unique identifier through multiple tables

--CELKO-- wrote:

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

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

Re: find unique identifier through multiple tables

>> 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!

Re: find unique identifier through multiple tables

--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!

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

Re: find unique identifier through multiple tables

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

Re: find unique identifier through multiple tables

Works perfectly!
Thanks a lot Dan !


On 22 sep, 16:35, "Dan Guzman"
wrote:
> > 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
>
> 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- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -