So you have an idea, you are pretty savvy at SQL, why don't you get access and create some scripts so you can pull the data directly from the database. After jumping through a few hoops with the DBA, you finally get access. You get in, and what do you see, about 100 tables with naming conventions you know nothing about. Yep, this ain't no data warehouse with names that align to your normal business terminology. No No No, you're in developer water now, the deep end with no resources. So now you have to figure out how the tables relate to one another.
You could do numerous trial and errors, but you're smart. You know there are already people out there like me that have already gone through this exact dilemma, and you are exactly right. So below is a script that I use when I know nothing about a database. The script below will provide the Foreign Keys in the tables that will allow you to determine how they relate. I got this from Pinal Dave.
SELECT t.name AS FKTableName
, fk.name AS NameOfForeignKey
, pc.name AS FKColumn
, rt.name AS ReferencedTable
, c.name AS ReferencedColumn
FROM sys.foreign_key_columns
AS fkc
INNER JOIN sys.foreign_keys AS fk ON fkc.constraint_object_id =
fk.object_id
INNER JOIN sys.tables AS t ON fkc.parent_object_id = t.object_id
INNER JOIN sys.tables AS rt ON fkc.referenced_object_id =
rt.object_id
INNER JOIN sys.columns AS pc ON fkc.parent_object_id = pc.object_id
AND fkc.parent_column_id = pc.column_id
INNER JOIN sys.columns AS c ON fkc.referenced_object_id =
c.object_id
AND fkc.referenced_column_id
= c.column_id
SELECT
t.NAME AS TableName,
p.rows AS RowCounts
FROM
sys.tables t
INNER JOIN
sys.partitions p ON t.object_id = p.OBJECT_ID
WHERE
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND p.rows = 0
GROUP BY
t.Name, p.Rows
ORDER BY
t.Name
