Three ways to find the right view in your SCCM Database
When it comes to data in SCCM, there is no shortage of what you can get access to, but where exactly do you find it? Below I'll outline three methods I've used in the past.
First, the TechNet documentation. I'm going to be honest, I haven't actually looked here in quite some time. It's available, but since I've been querying SCCM for quite some time, I've memorized most of the views. Except when I forget them, which is often, which is why I have two more options below.
Second, and most frequently, is that I query for views in the sysobjects table. The "right" way to do this is something like:
1select name from sysobjects where name like 'v_%memory%' and xtype = 'V'
But that's a lot to type, and I'm not writing reports, I'm just trying to get some quick information.
Instead, I simply write:
1select * from sysobjects where name like 'v%memory%'
You'll get back more than you need, but you can then quickly search this list for views that have to do with memory. Just replace the word memory with whatever you're looking for, such as
1select * from sysobjects where name like 'v%bios%'
This is more for just remembering the exact name for a view. If you're not even sure what you're looking for to begin with, you should probably start with the documentation.
The last method I hadn't actually considered before, is letting SQL Server Management Studio Intelligence find the view for you. This tip comes from Adam Gross via Twitter:
I’ll have to try that. When I’m in SQL Studio I type “Select * From memory” and intelligence generally shows anything with memory in the name.— Adam Gross (@AdamGrossTX) May 31, 2018
He also mentioned that if it doesn't work on first pass, try pressing CTRL+SPACE.
Got any tips to share? Throw them in the comments!