I have the WMI table name how to I find the corresponding SQL table?
The easiest way is to change the SMS_ to V_ and look around in the SQL Database. Lots of great articles about that on myitforum.
How about a query to match on partial table names:
Select correct database. Usually SMS_<SiteCode>
SELECT * FROM sysobjects WHERE name LIKE '%Compliance%' and xtype in ('U', 'V')
What is xtype?
- C = CHECK constraint
- D = Default or DEFAULT constraint
- F = FOREIGN KEY constraint
- L = Log
- P = Stored procedure
- PK = PRIMARY KEY constraint (type is K)
- RF = Replication filter stored procedure
- S = System table
- TR = Trigger
- U = User table
- UQ = UNIQUE constraint (type is K)
- V = View
- X = Extended stored procedure
One last thing to try. There is a table in SMS 2.0, SMS 2003 and ConfigMgr that displays collection queries in both WQL (what you see in the console) and SQL.
SELECT CollectionID, QueryKey, WQL, SQL
FROM dbo.Collection_Rules_SQL