SCCM Database Queries: Find Executable Files by Name, Version and Install Count

Find all instances of a particular file by its distinct Version number (e.g. "FileVersion" property).  In this example, I'm looking for what versions of Sysinternals' PsExec.exe are in the environment, and how many instances were found for each version.

[Begin T-SQL]
SELECT DISTINCT 
[ExecutableName0],[FileVersion0], COUNT(*) AS QTY
FROM [dbo].[v_GS_INSTALLED_EXECUTABLE]
WHERE ExecutableName0 = 'psexec.exe'
GROUP BY ExecutableName0, FileVersion0
ORDER BY FileVersion0
[End T-SQL]

Find Configuration Manager Collections with Both Direct and Query Rule Members

List all Collections which have both Direct members and Query-Rules assigned within your SCCM 2012 Site.  This also works with Configuration Manager 2007...

[begin T-SQL]
SELECT DISTINCT 
dbo.v_CollectionRuleDirect.CollectionID, 
dbo.v_Collection.Name
FROM 
dbo.v_CollectionRuleDirect INNER JOIN
dbo.v_CollectionRuleQuery ON dbo.v_CollectionRuleDirect.CollectionID = dbo.v_CollectionRuleQuery.CollectionID INNER JOIN
dbo.v_Collection ON dbo.v_CollectionRuleDirect.CollectionID = dbo.v_Collection.CollectionID
ORDER BY dbo.v_Collection.Name
[end T-SQL]

SQL Case Statements for Sum Operations

SELECT DISTINCT TOP (100) PERCENT
      room_type, COUNT(room_id) AS rooms, SUM(room_area) AS sqft,
      CASE
                WHEN room_type = 1 THEN 'Office'
                WHEN room_type = 2 THEN 'Storage'
                WHEN room_type = 3 THEN 'Equipment'
                WHEN room_type = 4 THEN 'Computer'
                WHEN room_type = 5 THEN 'Medical'
                WHEN room_type = 6 THEN 'Public'
                ELSE 'Unknown'
        END AS TypeName
FROM dbo.tbl_rooms
GROUP BY room_type
ORDER BY room_type

Returns something like the following:

room_type rooms     sqft
Office      210    6,540
Storage      54      983
Equipment    86    1,002
Computer      3      852
Medical     640   39,553
Public        6   10,401
Unknown       0        0

SQL Script Exports

Until the day comes when Microsoft SQL Server SSMS provides the same info export features that PHPMyAdmin provides, we’re stuck with the right-click “Script-To…” feature (unless you buy a third-party utility, or write your own SMO or ADOX/ADO scripts, etc.), I tripped and fell over a rather simple feature that I didn’t know existed.  You can export multiple table schemas in one operation by simply clicking on the “Tables” object in the Object Explorer panel, then right-click and choose “Script-To: CREATE” and save to the clipboard/file/query window.  It works the same as individual exports, only that this does all of the Tables at once.  You can do the same by clicking on the “Views” object as well.

If you already knew this, just chuckle, shrug and blow it off and move on.  No harm done.  I wasn’t aware of this, so I figured at least one other person might not know this as well.

KiXtart: Enumerate Access Database Tables with ADOX

$dbfile = "\\servername\share\folder\database.mdb"

Function ListTablesADOX($db)
    Dim $Conn, $strConn, $Catalog, $Table, $Column
    $strConn = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source=$db"
 
    ? "Database: $db @CRLF"
 
    $Conn    = CreateObject("ADODB.Connection")
    $Catalog = CreateObject("ADOX.Catalog")
    $Table   = CreateObject("ADOX.Table")
    $Column  = CreateObject("ADOX.Column")
 
    $Conn.Open($strConn)
    $Catalog.ActiveConnection = $Conn

    For Each $Table In $Catalog.Tables
        ? "Table: " + $Table.Name
        For Each $Column In $Table.Columns
            ? Chr(9) + "Column: " + $Column.Name
        Next
    Next
    $Conn.Close
EndFunction

$=ListTablesADOX($dbfile)