April 12, 2011 at 1:47 pm
I have a database that does not have any referential integrity, so I am trying to build a process.
Basically I would like to identify each column that is setup as a uniqueidentifier data type, which is no problem.
What I need some assistance with is to figure out the following:
Table1
- ColumnA - GUID
- ColumnD - GUID
Table2
- ColumnC - GUID
- ColumnE - GUID
Table3
- ColumnA - GUID
- ColumnB - GUID
So what I would like to do is get the GUID (actual record GUID) from Table1/ColumnA and loop through Table2 and 3 and see if that GUID exists CHILD. Then if there is multiple GUID columns on a table check the other GUID against all other tables. So trying to identity PARENT/CHILD relationships through the whole database.
This probably make no sense, but I thought I would try.
thanks
ss
April 12, 2011 at 2:35 pm
I do not beleive that would show what you are looking for. Even if the child record existed in table 2 I would not expect to find a relationship in the GUID. I believe the GUID is unique to the record and that record alone. Infact I beleive you could create two tables with completely identical rows and the GUID would not match. or atleast that is my undetstanding.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 12, 2011 at 2:42 pm
The thought is that the child table would have the same GUID inserted from the parent record. Like a Primary Key / Foreigh Key relationship.
April 12, 2011 at 2:45 pm
I could be horribly wrong as I have not done extensive research on GUID's but I do not beleive that would be the case. The GUID is generated per record and I do not beleive any relationship figures into that.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
April 12, 2011 at 2:49 pm
I understand what you are saying, but I think that would only relate to a uniqueidentifier field that is set to have an auto-enter identity. On the foreign key side I can have a uniqueidentfier column that is not identity that can have a GUID entered in the column that is a variable from the parent record.
April 12, 2011 at 2:52 pm
Dan's right where whenever GUID's are generated with a newId() function, they are unique, but if the business process was using them for FK equivilents, you can find out easy enough with a IN statement
--Table1/ColumnA and loop through Table2 and 3
SELECT * FROM Table2 WHERE ColumnWhatever IN(SELECT ColumnA FROM TableA)
any reuslt sshows the GUID was being used for some kind of reference...whether it's parent child or not would require investigaiton, of course.
Lowell
April 12, 2011 at 2:53 pm
Dan, are you thinking of the row ID SQL Server uses internally? I don't think we can touch those through the standard means anyway. Some people explicitly use GUIDs as their PK.
sqlsponge2, This query will show you all tables that have columns storing the same GUID. Note that this compares all columns with a UNIQUEIDENTIFIER type to all other columns which can take a really long time if run in a database where tables with GUIDs have a large number of rows and the GUID column is not present as the first column in the index.
DECLARE @sql NVARCHAR(MAX) = '' ;
SELECT @sql = @sql + 'IF EXISTS (SELECT * FROM ' + OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id) + ' t1 JOIN '
+ OBJECT_SCHEMA_NAME(c2.object_id) + '.' + OBJECT_NAME(c2.object_id) + ' t2 ON t1.' + c.name + ' = t2.' + c2.name + ') PRINT '''
+ OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id) + '.' + c.name + ' <==> ' + OBJECT_SCHEMA_NAME(c2.object_id) + '.'
+ OBJECT_NAME(c2.object_id) + '.' + c2.name + '''
'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
CROSS JOIN sys.columns c2
JOIN sys.tables t2 ON c2.object_id = t2.object_id
WHERE c.system_type_id = 36 -- uniqueidentifier
AND c2.system_type_id = 36 -- uniqueidentifier
AND (
c.object_id != c2.object_id
OR c.column_id != c2.column_id
)
AND t.is_ms_shipped = 0
AND t2.is_ms_shipped = 0
AND t.object_id < t2.object_id ;
EXEC(@sql) ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply