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. 

sqlviews
SELECT DISTINCT

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',
CASE
WHEN SO.name LIKE 'v[_]RA[_]%' THEN 'Resource Array'
WHEN SO.name LIKE 'v[_]R[_]%' THEN 'Resource'
WHEN SO.name LIKE 'v[_]HS[_]%' THEN 'Inventory History'
WHEN SO.name LIKE 'v[_]GS[_]%' THEN 'Inventory'
WHEN SO.name LIKE 'v[_]CM[_]%' THEN 'Collection'
WHEN SO.name LIKE '%Summ%' THEN 'Status Summarizer'
WHEN SO.name LIKE '%Stat%' THEN 'Status'
WHEN SO.name LIKE '%Permission%' THEN 'Security'
WHEN SO.name LIKE '%Secured%' THEN 'Security'
WHEN SO.name LIKE '%Map%' THEN 'Schema'
WHEN SO.name = 'v_SchemaViews' THEN 'Schema'
ELSE 'Other'
END AS 'Type',
SO.name AS 'ViewName'

FROM
--Role/Member associations
sys.database_role_members AS DBM
INNER JOIN sys.database_principals AS ROLE ON ROLE.principal_id = DBM.role_principal_id
--Roles
LEFT OUTER JOIN sys.database_permissions  AS PERM ON PERM.grantee_principal_id = ROLE.principal_id
--Permissions
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.id

WHERE

So.type IN ('IF','V')
AND SO.name NOT LIKE 'v_CM_RES_COLL%'
AND SO.name NOT LIKE 'fn_RBAC_CM_RES_COLL%'
AND ROLE.name = 'smsschm_users'
ORDER BY
1,
SO.name

SCCMSupportedSQLViews

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