No Data Dictionary, No Problem!!!!

        So, you know how this goes, management gets dazzled into buying a new application. All they know is that it creates beautiful reports that will revolutionize their business. What they don't know from the 50,000 foot view from the top is that the majority of the reports contain absolutely no detail, and to get to the detail takes numerous clicks of the mouse. And to add an extra degree of difficulty, the reports are PDFs.

        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

  in addition, use this script below to identify tables with no data, so you don't have to waste time with them.

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