December 12, 2011 at 6:23 pm
Hi,
Is it possible to take two tables and see how they are connected?
I have an application name listed in one table and a support group listed in another table and somehow they are related via some in between tables but I can't work out how.
December 12, 2011 at 9:32 pm
PHXHoward (12/12/2011)
Hi,Is it possible to take two tables and see how they are connected?
I have an application name listed in one table and a support group listed in another table and somehow they are related via some in between tables but I can't work out how.
If there is no DRI (Declared Referential Integrity) between the tables in the form of Primary and Foreign Keys, the you can try searching for both table names in sys.sql_modules to, perhaps, find a piece of code that identifies the relationship. You can also look at the column names. If the column name of the PK of one table is found in the other table, you might have the relationship sussed. That's provided they used a good naming convention and followed it. Other than that, you can do a deep dive on the data itself hoping to find a glimmer of a connection. After that and quite frequently, either there is no direct connection and you have to look for a "bridging" or "associative" table that connect the two, or there's simply nothing common between the tables.
If someone did a reasonable job with DRI, you can use sys Diagrams and let it literally draw the relationship for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 12, 2011 at 10:51 pm
Thanks Jeff, poking around in the tables gave me some clues and then I opened a new database diagram and added the tables and then was able to see where the relationships fit together.
Really appreciate the ideas.
December 13, 2011 at 5:33 am
You bet. Thank you for the feedback.
If you ever design tables, remember what you just went through. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
December 13, 2011 at 5:44 am
PHXHoward (12/12/2011)
Thanks Jeff, poking around in the tables gave me some clues and then I opened a new database diagram and added the tables and then was able to see where the relationships fit together.Really appreciate the ideas.
Adding a word of caution here. Please don't press DEL if you are in Database Diagram windows. It will delete the database object or relationship (with / without warning). It's just not a diagram; it's your database physical model. Any changes to it will be reflected in your database.
December 13, 2011 at 5:56 am
Also, with some human intelligence, this sort of script can help identify potential relationships.
SELECT object_schema, object_name, CASE WHEN dataobjecttype = 'P'
THEN '@'
ELSE '' END + parameter AS field, datatype
FROM (SELECT Object_schema_name(objectid) AS object_schema, Object_name(objectid) AS object_name, parameter,
typename + ' ' + CASE WHEN typename IN ('char', 'varchar', 'nchar', 'nvarchar')
THEN '(' + CASE WHEN length = - 1
THEN 'MAX'
ELSE CONVERT(VARCHAR(4), CASE WHEN TypeName IN ('nchar', 'nvarchar')
THEN length / 2
ELSE length
END)
END + ')'
WHEN typename IN ('decimal', 'numeric')
THEN '(' + CONVERT(VARCHAR(4), precision) + ',' + CONVERT(VARCHAR(4), scale) + ')'
ELSE ''
END + CASE WHEN XML_collection_ID <> 0
THEN '(' + CASE WHEN is_XML_Document = 1
THEN 'DOCUMENT '
ELSE 'CONTENT '
END + COALESCE((SELECT TOP 1 QUOTENAME(ss.NAME) + '.' + QUOTENAME(sc.NAME)
FROM sys.xml_schema_collections sc
INNER JOIN Sys.Schemas ss ON sc.schema_ID = ss.schema_ID
WHERE sc.xml_collection_ID = XML_collection_ID), 'NULL') + ')'
ELSE ''
END AS datatype, dataobjecttype, NULLIF(collation_name, 'parameter') AS collation_name
FROM (SELECT t.NAME AS typename, REPLACE(c.NAME, '@', '') AS parameter, c.max_length AS length, c.precision AS precision, c.scale AS scale,
c.object_id AS objectid, xml_collection_id, is_xml_document, 'P' AS dataobjecttype, 'parameter' AS collation_name
FROM sys.parameters c
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id AND parameter_id > 0
UNION ALL SELECT t.NAME AS typename, c.NAME AS parameter, c.max_length AS length, c.precision AS precision, c.scale AS scale,
c.object_id AS objectid, xml_collection_id, is_xml_document, 'C' AS dataobjecttype, ISNULL(c.collation_name, 'None') AS collation_name
FROM sys.columns c
INNER JOIN sys.types t ON c.user_Type_ID = t.user_Type_ID
WHERE OBJECT_SCHEMA_NAME(c.object_ID) <> 'sys') innerQuery) outerQuery
ORDER BY parameter
December 13, 2011 at 6:12 am
Reformatted for CS servers.
Also reformatted with my tools.
SELECT
object_schema
, object_name
, CASE WHEN dataobjecttype = 'P' THEN '@'
ELSE ''
END + parameter AS field
, datatype
FROM
(
SELECT
Object_schema_name(objectid) AS object_schema
, Object_name(objectid) AS object_name
, parameter
, TypeName + ' '
+ CASE WHEN TypeName IN ( 'char' , 'varchar' , 'nchar' , 'nvarchar' )
THEN '(' + CASE WHEN length = -1 THEN 'MAX'
ELSE CONVERT(VARCHAR(4) , CASE WHEN TypeName IN ( 'nchar' , 'nvarchar' ) THEN length / 2
ELSE length
END)
END + ')'
WHEN TypeName IN ( 'decimal' , 'numeric' )
THEN '(' + CONVERT(VARCHAR(4) , precision) + ','
+ CONVERT(VARCHAR(4) , scale) + ')'
ELSE ''
END + CASE WHEN xml_collection_id <> 0
THEN '(' + CASE WHEN is_XML_Document = 1 THEN 'DOCUMENT '
ELSE 'CONTENT '
END
+ COALESCE((
SELECT TOP 1
QUOTENAME(ss.NAME) + '.'
+ QUOTENAME(sc.NAME)
FROM
sys.xml_schema_collections sc
INNER JOIN sys.schemas ss
ON sc.schema_ID = ss.schema_ID
WHERE
sc.xml_collection_id = xml_collection_id
) , 'NULL') + ')'
ELSE ''
END AS datatype
, dataobjecttype
, NULLIF(collation_name , 'parameter') AS collation_name
FROM
(
SELECT
t.NAME AS TypeName
, REPLACE(c.NAME , '@' , '') AS parameter
, c.max_length AS length
, c.precision AS precision
, c.scale AS scale
, c.object_id AS objectid
, xml_collection_id
, is_XML_Document
, 'P' AS dataobjecttype
, 'parameter' AS collation_name
FROM
sys.parameters c
INNER JOIN sys.types t
ON c.user_type_id = t.user_type_id
AND parameter_id > 0
UNION ALL
SELECT
t.NAME AS TypeName
, c.NAME AS parameter
, c.max_length AS length
, c.precision AS precision
, c.scale AS scale
, c.object_id AS objectid
, xml_collection_id
, is_XML_Document
, 'C' AS dataobjecttype
, ISNULL(c.collation_name , 'None') AS collation_name
FROM
sys.columns c
INNER JOIN sys.types t
ON c.user_Type_ID = t.user_Type_ID
WHERE
OBJECT_SCHEMA_NAME(c.object_ID) <> 'sys'
) innerQuery
) outerQuery
ORDER BY
parameter
, field
, object_schema
, object_name
December 13, 2011 at 10:19 am
Thanks everyone. The queries and suggestions really helped. In the end, it was not that complex of a join but the database (Microsoft Project Portfolio Server) is hugely normalized with hundreds of tables so tracking down the connections was complicated for me.
It ended up like this:
select se.Name, ad._Value
from sfATTRIBUTES a
join sfATTRIBUTE_DEFINITIONS ad on ad.attributeID = a.ID
join sfATTRIBUTE_VALUES av on a.id = av.AttributeID and av.DefinitionID = ad.ID
join sfSTRUCTURE_ENTRIES se on se.ID = av.Structure1EntryID
where a.name = 'Responsible Org' and se.name like '%<name>%'
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply