Hi guys,

For most of the BI developers, getting BI related tasks (SSAS, SSIS, SSRS etc.) from multiple teams in company is very common. So in this situation, developer completes a task(s) for one team and goes to another team for different task(s). Once you go to the team for their requirements … lets say for few SSRS reports development or ELT Jobs, you will realize that, they neither have time to give you the proper knowledge transfer nor they could explain the requirements up to the mark how we wanted know in detail. And you have to give the estimate of your work, plan etc.

Well, so the first thing as for a BI developer to know is the database design, team is using and relationships between tables. Here I want to share my 2 favorite ways to find-out the table relationships in database.

1. Database Diagram
2. Reverse Engineer Database using Visio

Ways to use these options:

Database Diagram

1. Expand your database tree in SQL Server Management Studio (IDE).

2. Right click “Database Diagrams” and Select “New Database Diagram”

3. You will see a dialog-box which lists all the available tables in selected database. Select all/few tables and click Add.

4. That’s it, it will generate database diagram. You can use the right-bottom button to navigate the diagram if it’s too big to fit in your screen.

 

Reverse Engineer Database using Visio

1. Open Microsoft Office Visio.  Select “File” menu -> “New” -> “Software and Database” -> “Database Model Diagram (Metric)”.

2. Let the empty sheet load. Select “Database” menu -> “Reverse Engineer”

3. This will pop-up the “Reverse Engineer Wizard”. Choose “Generic OLE DB Provider” in “Installed Visio drivers” category. (If you don’t see this value then use “Setup..” button)

4. Click “Next”. You will see “Data Link Properties” dialog box. Select “Microsoft OLE DB Provider for SQL Server” in Provider. And Click Next.

5. In “Connection” tab, provide your Server Name, Database Name and required credentials. Test the connection for verification.

Tip: If you have many databases in your server instance, better write the database name in point number 3. Or copy-paste database’s name here. Otherwise it will query SMO objects to dynamically find-out how many databases are there in your selected Server instance. And this could take a whileJ. Same thing applies to Point number 1. If you have many database server/instances, then you better copy-paste the name rather than selecting it from the dropdown list. Sometimes it also hangs in cases of network travelling, server not responding etc.

6. Then click “Finish” and you will see “Reverse Engineer Wizard” dialog box again. Here you select everything except Views and Stored Procedures. Because we need only database diagram. Click Next.

7. Hit “Select All” button to select all the tables. (If you want to check relationships between only particular tables then select only them.) Click Next.

8. Select first radio button which says “Yes, add the shapes to the current page”. (You can also select the second one and customize the designed diagram your way, but here our motive is to fasten the process to know the table relationships). Click Finish.

9. You will see following screen with the database diagram you wanted.

I am pretty sure, there lots of ways, but believe me these are very quick ways. We can also write custom SQL queries based on our personal choice (how we want to know and see the information about database) and re-use that SQL queries whenever required.

You can also check this query for your reference: http://blog.namwarrizvi.com/?p=232

Or My Query 🙂

Use <Your Database Name>
Go

SELECT
OBJECT_NAME(RKEYID) PARENT_TABLENAME,
SC1.NAME FOREIGNKEY_COLUMNNAME,
OBJECT_NAME(FKEYID) CHILD_TABLENAME,
SC2.NAME REF_KEYCOL,
OBJECT_NAME(CONSTID) FOREIGNKEY_NAME
FROM
SYS.SYSFOREIGNKEYS SFK
LEFT OUTER JOIN
SYS.SYSCOLUMNS SC1
ON ( SFK.FKEYID = SC1.ID AND SFK.FKEY = SC1.COLID )
INNER JOIN
SYS.SYSCOLUMNS SC2
ON ( SFK.RKEYID = SC2.ID AND SFK.RKEY = SC2.COLID )
ORDER BY PARENT_TABLENAME, CHILD_TABLENAME
Go

Hope you will find this useful.

Thanks, Khilit