Skip to main content

Simple SQL Query to list all SCSM Relationship types

Author by Lee Berg

During the course of various Service Manager integration projects and reporting, I have found myself using a simple query to quickly and easily lookup all the various existing SCSM relationship types to create a "relationship reference list' I then use this list to effortlessly lookup the various relationships and GUIDs to use in my SQL Queries.
SELECT DISTINCT
R.RelationshipTypeId, DS.DisplayName
FROM Relationship R
INNER JOIN DisplayStringView DS On R.RelationshipTypeId = DS.LTStringId AND DS.LanguageCode = 'ENU'
ORDER BY DS.DisplayName

The results should look something like this:scsmqueryblog

This will return a list of EXISTING relationships, and is helpful to check for existing relationships.

To show all relationships types (even if a relationship if that type does not yet exist) you can simply query the RelationshipType Table
SELECT
R.RelationshipTypeId, R.RelationshipTypeName

FROM RelationshipType R

ORDER BY R.RelationshipTypeName
You might want to tweak this to meet your needs but I find it very handy. I typically save the results out to a OneNote page for future reference. You may also notice that many RelationshipTypeIDs carry over across environments!    
Author

Lee Berg

Systems Engineer