Infrastructure Blog

Finding Supported SCCM SQL Views​

I hate using random SQL Queries off the internet without understanding what the query is doing.  One reason why it is dangerous to use a random queries off the internet without testing in your own lab environment is because a bad query has the potential to cause locking on the database.

I stumbled upon a fantastic blog post by Garth Jones which identifies all the supported views in SCCM.  See link to his original article at the bottom of this page.

Use the query below (test it in your own lab environment) to find out the Supported SQL Views and Functions in your SCCM environment.  If you’re impatient or do not have access to your lab environment, you will see a list of the supported views and functions I have discovered in my own lab environment. 


CASE So.type
WHEN 'FN' THEN 'SQL scalar function'
WHEN 'FS' THEN 'Assembly (CLR) scalar-function'
WHEN 'FT' THEN 'Assembly (CLR) table-valued function'
WHEN 'IF' THEN 'Table Function (RBA)'
WHEN 'P' THEN 'SQL Stored Procedure'
WHEN 'S' THEN 'System base table'
WHEN 'U' THEN 'Table'
WHEN 'V' THEN 'View'
WHEN 'SQ' THEN 'Service queue'
WHEN 'TF' THEN 'Table Function (RBA??)'
ELSE so.type
END AS 'Object type',
WHEN LIKE 'v[_]RA[_]%' THEN 'Resource Array'
WHEN LIKE 'v[_]R[_]%' THEN 'Resource'
WHEN LIKE 'v[_]HS[_]%' THEN 'Inventory History'
WHEN LIKE 'v[_]GS[_]%' THEN 'Inventory'
WHEN LIKE 'v[_]CM[_]%' THEN 'Collection'
WHEN LIKE '%Summ%' THEN 'Status Summarizer'
WHEN LIKE '%Stat%' THEN 'Status'
WHEN LIKE '%Permission%' THEN 'Security'
WHEN LIKE '%Secured%' THEN 'Security'
WHEN LIKE '%Map%' THEN 'Schema'
WHEN = 'v_SchemaViews' THEN 'Schema'
ELSE 'Other'
END AS 'Type', AS 'ViewName'

--Role/Member associations
sys.database_role_members AS DBM
INNER JOIN sys.database_principals AS ROLE ON ROLE.principal_id = DBM.role_principal_id
LEFT OUTER JOIN sys.database_permissions  AS PERM ON PERM.grantee_principal_id = ROLE.principal_id
LEFT OUTER JOIN sys.columns AS COL ON col.object_id = PERM.major_id AND COL.column_id = PERM.minor_id
--Table columns
LEFT OUTER JOIN sys.objects AS OBJ ON PERM.major_id = OBJ.object_id
LEFT OUTER JOIN sysobjects AS SO ON PERM.major_id =


So.type IN ('IF','V')
AND = 'smsschm_users'

To Read more about it, check out Garth Jones Blog post here

wdt_ID Object type Type ViewName
1 Table Function (RBA) Other fn_AllDTRelationships
2 Table Function (RBA) Status Summarizer fn_AppClientSummarizedState
3 Table Function (RBA) Other fn_AppDeploymentAssetDetails
4 Table Function (RBA) Other fn_AppDeploymentErrorAssetDetails
5 Table Function (RBA) Status fn_AppDeploymentErrorStatus
6 Table Function (RBA) Other fn_AppDeploymentLaunchErrorAssetDetails
7 Table Function (RBA) Other fn_AppDeploymentRNMAssetDetails
8 Table Function (RBA) Status fn_AppDeploymentRNMStatus
9 Table Function (RBA) Status fn_AppDeploymentStatus
10 Table Function (RBA) Status Summarizer fn_AppDTClientSummarizedState
Object type Type ViewName
Share on facebook
Share on twitter
Share on linkedin

Sorry, but I had to.

I use cookies and similar technologies to run this website to help me understand how you use it. Privacy Policy