Querying SOLIDWORKS PDM Database – SQL Table Diagrams

This is part of a series of documents aimed towards providing the basics of creating queries for SOLIDWORKS PDM vault databases. Administrators will be able to collect data for their own purposes that are not attainable from SOLIDWORKS PDM out-of-the-box search features. 

This document also provides a way to graphically see the relationships between vault database tables to then plan an approach for building a query. Use these steps to generate a diagram of table relationships for your specific vault database using Microsoft SQL Server Management Studio (SSMS).

Why are table relationships important? 

For the basic level of queries that you will be able to create with this series, you would need to understand why table relationships are important. There are many relationships between tables in a vault database. Here are two to illustrate how tables relate. If you need to query the ID of the folder in which the file is stored, you would need to “Join” two SQL tables. The “Documents” table which has the file name, and the “DocumentsInProjects” table which has the ID of the folder. 

Joining the DocumentID column for both the output query is the following. 

We have now successfully joined the two tables and see the corresponding ProjectID for the files you are interested in. Using a “WHERE” clause, you can further “filter” the results. This type of joining is called INNER JOIN. It is shown by the connecting line between the two tables in a diagram.

Creating SOLIDWORKS PDM Vault Database Diagrams 

1.  Access the PDM Vault database through SSMS. It is recommended to log in as a read only user.
   
2. Navigate: Databases > your vault database name > right mouse click on database diagrams > Select New Database Diagram
   
   
   
   

   Click Yes if prompted to create the necessary support objects.
   
   
   

3.  After the Add Table window appears, select the tables of interest.
   
   
   
   

   The most common tables to query are: 
   

   Documents 

   TransitionHistory 

   DocumentsInProjects 

   Transitions 

   GroupMembers 

   UserRevs 

   Groups 

   Users 

   HistoryRename 

   Variable 

   Project Tree 

   Variable Value 

   Projects 

   Workflows 

   Revisions 

   Xrefs 

   Status

4.  

   It may take SSMS a few moments to complete the diagram – depending on how many tables you have chosen. After all the desired tables have been selected, click Close. Inspect the diagram and adjust by dragging the tables or relation lines as you see necessary. Below is an example of a diagram showing connections between the Documents, DocumentsInProjects, and Projects tables.
   
   
   

5.  To save the diagram as an image for later use, you can right mouse click on any white space of the diagram and select Copy Diagram to Clipboard.
   
   
   
   
6. Open Microsoft Paint to paste the Diagram using the Ctrl+V keys on your keyboard.
   1. Start Menu > Search for “Paint”.
      
      Note that if you have a large diagram, you may need to zoom in on the diagram before copying into clipboard to increase detail. For example, On the left diagram, no zoom was performed. On the right, a zoom was performed prior to copying to clipboard.
      
      
      
7. Save the image and close SSMS.

I hope you found this article helpful. For additional content like this, check out the related articles below.

Related Articles

Working Offline with SOLIDWORKS PDM Tips and Tricks 

How to Reset the SQL 'SA' User Password in SOLIDWORKS PDM

SOLIDWORKS PDM Upgrading SQL 2014 to Service Pack 3

Moving the SOLIDWORKS PDM Vault Database Yourself? Start Here