find in which table a uniqueidentifier is

Question

Post reply

find in which table a uniqueidentifier is

Scal

SSC Rookie

Points: 34
More actions
December 29, 2007 at 11:14 am

#377750

Hello everyone;
I'ld like to know how I could find in which table a uniqueidentifier is.
I believe I would have to look at this using the sys objects but not sure I'm going in the right direction.
Thanks
Scal

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Nah... they've got views for that stuff...

SELECT *

FROM Information_Schema.Columns

WHERE Data_Type = 'UniqueIdentifier'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Scal SSC Rookie Points: 34 More actions · Answer 2

Thank you Jeff;

I meant based on a GUID value. Let's say I know I have a record with a PK of '0BE9D6D4-3F39-424A-BCB7-DE0D84B51B64' but wish to know in which table it's stored.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You'll have to query every table. There's no place where all data from all columns is stored, or anything like that.

You can use the system tables/information views to build up the queries for you.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 4

Gail is correct, you need to query every table with a GUID. And every column.

Use Jeff's query to find the tables/columns and then query each of those.

Remaks SSC Enthusiast Points: 171 More actions · Answer 5

Try this. It could be used to find your unique identifiers, or any value in a table (must be an exact match, and may act weirdly with untested field types). It would work very slowly on large DBs or if you're matching against a common field type, but it should work OK for what you need. The call used to find a value as you suggest is at the bottom.

CREATE PROCEDURE dbo.usp_FindValue

@valueType SYSNAME

, @value VARCHAR(1000)

AS

SET NOCOUNT ON

CREATE TABLE #found ( SchemaName SYSNAME

, TableName SYSNAME

, ColumnName SYSNAME)

DECLARE @schema SYSNAME

, @table SYSNAME

, @column SYSNAME

, @sql varchar(8000)

, @cur CURSOR

SET @cur = CURSOR FOR

SELECT table_schema, table_name, column_name

FROM Information_Schema.Columns

WHERE Data_Type = @valueType

OPEN @cur

FETCH FROM @cur INTO @schema, @table, @column

WHILE @@fetch_status = 0

BEGIN

SET @sql = 'SELECT ''' + @schema + ''', ''' + @table + ''', ''' + @column + ''' from [' + @schema + '].[' + @table + ']

WHERE [' + @column + '] = ''' + @value + ''''

INSERT INTO #found

EXEC (@sql)

FETCH NEXT FROM @cur INTO @schema, @table, @column

END

CLOSE @cur

DEALLOCATE @cur

SET NOCOUNT OFF

SELECT DISTINCT * FROM #found

SET NOCOUNT ON

DROP TABLE #found

SET NOCOUNT OFF

GO

EXEC dbo.usp_FindValue 'UniqueIdentifier', '0BE9D6D4-3F39-424A-BCB7-DE0D84B51B64'

Scal SSC Rookie Points: 34 More actions · Answer 6

Scal

SSC Rookie

Points: 34

January 2, 2008 at 6:01 am

#763999

Thanks a lot of the help guys!