SQL-Query to return ‘All Entitlements and their content that are assigned to a specific Key Id’

Input Parameter:  Key Id

Output :

  1. Customer Name (CSTMRName)
  2. Entitlement ID (ENTId, EId)
  3. Start date of Entitlement (StartDate)
  4. End date of Entitlement (EndDate)
  5. Reference ID 1 (RefId1)
  6. Reference ID 2 (RefId2)
  7. Key Type (KeyTypeId)
  8. Hasp ID (HASPId)
  9. Product ID (PrdId)
  10. Product Name (PRDName)
  11. Product Version (productVersion)
  12. Feature ID (FTRId)
  13. Feature Name (FTRName)
  14. License  Model (LMName)

Note: The exact ‘Column Names’ (from the below mentioned EMSDB Tables) are mentioned in bracket(s), as above. 

  1. T_ENT
  2. T_ENT_LINE_ITEM
  3. T_ENT_ACTIVATION
  4. T_TARGET
  5. T_ENT_LINE_ITEM_PRD_SUITE
  6. T_ENT_FTR_LM
  7. T_LM
  8. T_PRD
  9. T_PRD_FTR_REF
  10. T_FTR

 

SQL-Query is as follows :

 

DECLARE @KEY_ID varchar(255);

SET @KEY_ID = ‘Enter Key Id’;

 

SELECT Top 1 CSTMRName, te.ENTId, EId, te.Descr, te.StartDate, te.EndDate, te.RefId1, te.RefId2, tt.KeyTypeId, tt.HASPId, telips.PrdId, PRDName, tp.Ver AS productVersion, tf.FTRId,FTRName, LMName FROM dbo.T_CSTMR tc

JOIN dbo.T_ENT te ON te.CSTMRId = tc.CSTMRId

JOIN dbo.T_ENT_LINE_ITEM teli ON teli.ENTId = te.ENTId

JOIN dbo.T_ENT_ACTIVATION tea ON tea.LineItemId = teli.LineItemId

JOIN dbo.T_TARGET tt ON tt.TargetId = tea.TargetId

JOIN dbo.T_ENT_LINE_ITEM_PRD_SUITE telips ON telips.LineItemId = teli.LineItemId

JOIN dbo.T_ENT_FTR_LM tefl ON tefl.PrdSuiteLineItemId = telips.PrdSuiteLineItemId

JOIN dbo.T_LM tl ON tl.LMId = tefl.LMId

JOIN dbo.T_PRD tp ON tp.PRDId = telips.PrdId

JOIN dbo.T_PRD_FTR_REF tpfr ON tpfr.PRDId = tp.PRDId

JOIN dbo.T_FTR tf ON tf.FTRId = tpfr.FTRId

 

WHERE (tt.HASPId = @KEY_ID)