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_IDObject typeTypeViewName
1Table Function (RBA)Otherfn_AllDTRelationships
2Table Function (RBA)Status Summarizerfn_AppClientSummarizedState
3Table Function (RBA)Otherfn_AppDeploymentAssetDetails
4Table Function (RBA)Otherfn_AppDeploymentErrorAssetDetails
5Table Function (RBA)Statusfn_AppDeploymentErrorStatus
6Table Function (RBA)Otherfn_AppDeploymentLaunchErrorAssetDetails
7Table Function (RBA)Otherfn_AppDeploymentRNMAssetDetails
8Table Function (RBA)Statusfn_AppDeploymentRNMStatus
9Table Function (RBA)Statusfn_AppDeploymentStatus
10Table Function (RBA)Status Summarizerfn_AppDTClientSummarizedState
Object typeTypeViewName
Close Menu

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