|
|
Posted by hugues.morel on 09/29/07 07:13
Works perfectly!
Thanks a lot Dan !
On 22 sep, 16:35, "Dan Guzman" <guzma...@nospam-online.sbcglobal.net>
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
>
> <hugues.mo...@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- Tekst uit oorspronkelijk bericht niet weergeven -
>
> - Tekst uit oorspronkelijk bericht weergeven -
Navigation:
[Reply to this message]
|