Device Collection to find two Installed Applications

Select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client
from SMS_R_System
where
SMS_R_System.Name in (Select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName = "Symantec Endpoint Protection")
and SMS_R_System.Name in (Select SMS_R_System.Name from SMS_R_System inner join SMS_G_System_INSTALLED_SOFTWARE on SMS_G_System_INSTALLED_SOFTWARE.ResourceID = SMS_R_System.ResourceId where SMS_G_System_INSTALLED_SOFTWARE.ARPDisplayName = "CrowdStrike Windows Sensor")

SQL Query to find if Full-Text Indexing is enabled in SQL Server

Please find the SQL Query to find if Full-Text Indexing is enabled in SQL Server

SELECT
    CASE WHEN FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') = 1
    THEN 
        'INSTALLED'
    ELSE 
        'NOT INSTALLED'
END IsFullTextInstalled;

Please find an alternated SQL query as follows:

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
Print 'INSTALLED'
Else
Print 'NOT INSTALLED';

Reference: FULLTEXTSERVICEPROPERTY (Transact-SQL)

Other MS Article:
 Enable a Database for Full-Text Indexing (SQL Server Management Studio)

SQL Query to find Configuration Manager Application's Dependencies

In this blog we have provided the SQL query to find all the dependent applications of every application packaged in the Configuration Manager. We have joined Table-valued Functions such as fn_ListApplicationCIs & fn_ListDeploymentTypeCIs and vSMS_AppRelation_Flat view to get the expected output.

WMI class SMS_ApplicationAssignment server can be referred for other application assignment properties and values.
 

Database SQL Query: Find all table names with column name

SQL Server:

SELECT c.name as 'Column Name',t.name as 'Table Name'
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%<ColumnName>%'
ORDER BY TableName,ColumnName asc;

SELECT COLUMN_NAME as 'Column Name', TABLE_NAME as 'Table Name'

FROM  INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME LIKE '%<ColumnName>%'

ORDER BY    ColumnName, TableName asc;

SELECT name as 'Table Name'

FROM sysobjects 

WHERE id IN (SELECT id 

                          FROM syscolumns 

                          WHERE name like '%<ColumnName>%')

SELECT name as 'Table Name'

FROM sys.tables 

WHERE OBJECT_ID IN ( SELECT id 

                                               FROM syscolumns 

                                               WHERE name like '%<ColumnName>%' )

Oracle:

SELECT owner, table_name, column_name 

FROM all_tab_columns 

WHERE column_name LIKE '%<ColumnName>%' 

AND owner in ('<YOUR_SCHEMA_NAME>');

In above query replace <ColumnName> with any other column name.

If you simply want the table name you can run:

select object_name(object_id) from sys.columns where name like '%received_at%'


If you want the Schema Name as well (which in a lot of cases you will, as you'll have a lot of different schemas, and unless you can remember every table in the database and where it belongs this can be useful) run:

select OBJECT_SCHEMA_NAME(object_id),object_name(object_id) from sys.columns where name like '%received_at%'


and finally if you want it in a nicer format (although this is where the code (In my opinion) is getting too complicated for easy writing):

select concat(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) from sys.columns where name like '%received_at%'


note you can also create a function based on what I have:

CREATE PROCEDURE usp_tablecheck
--Scan through all tables to identify all tables with columns that have the provided string
--Stephen B
@name nvarchar(200)
AS
SELECT CONCAT(OBJECT_SCHEMA_NAME(object_id),'.',object_name(object_id)) AS [Table Name], name AS [Column] FROM sys.columns
WHERE name LIKE CONCAT('%',@name,'%')
ORDER BY [Table Name] ASC, [Column] ASC
GO

It is worth noting that the concat feature was added in 2012. For 2008r2 and earlier use + to concatenate strings.

I've re-formatted the proc a bit since I posted this. It's a bit more advanced now but looks a lot messier (but it's in a proc so you'll never see it) and it's formatted better.

This version allows you to have it in an administrative database and then search through any database. Change the decleration of @db from 'master' to whichever you want the default database to be (NOTE: using the CONCAT() function will only work with 2012+ unless you change the string concatenation to use the + operators).

CREATE PROCEDURE [dbo].[usp_tablecheck]
    --Scan through all tables to identify all tables in the specified database with columns that have the provided string
    --Stephen B
    @name nvarchar(200)
    ,@db nvarchar(200) = 'master'
AS
    DECLARE @sql nvarchar(4000) = CONCAT('
        SELECT concat(OBJECT_SCHEMA_NAME(col.object_id,DB_ID(''',@db,''')),''.'',object_name(col.object_id,DB_ID(''',@db,'''))) AS [Table Name]
            ,col.name AS [Column] 
        FROM ',@db,'.sys.columns col
        LEFT JOIN ',@db,'.sys.objects ob 
            ON ob.object_id = col.object_id
        WHERE 
            col.name LIKE CONCAT(''%'',''',@name,''',''%'') 
            AND ob.type =''U''
        ORDER BY [Table Name] ASC
            ,[Column] ASC')
    EXECUTE (@sql)
GO

Hopefully this isn't a duplicate answer, but what I like to do is generate a sql statement within a sql statement that will allow me to search for the values I am looking for (not just the tables with those field names ( as it's usually necessary for me to then delete any info related to the id of the column name I am looking for):

  SELECT  'Select * from ' + t.name + ' where ' + c.name + ' = 148' AS SQLToRun
  FROM sys.columns c, c.name as ColName, t.name as TableName
  JOIN sys.tables t 
     ON c.object_id = t.object_id
  WHERE c.name LIKE '%ProjectID%'

Then I can copy and paste run my 1st column "SQLToRun"... then I replace the "Select * from ' with 'Delete from ' and it allows me to delete any references to that given ID! Write these results to file so you have them just in case.
NOTE**** Make sure you eliminate any bakup tables prior to running your your delete statement...

  SELECT  'Delete from ' + t.name + ' where ' + c.name + ' = 148' AS SQLToRun
  FROM sys.columns c, c.name as ColName, t.name as TableName
  JOIN sys.tables t 
     ON c.object_id = t.object_id
  WHERE c.name LIKE '%ProjectID%'

Configuration Manager: WQL Query to find the machines based on Deployment Status

WQL Query to find the machines falls under unknown tab for any application or software update deployments:

Select SMS_R_SYSTEM.ResourceID,

SMS_R_SYSTEM.ResourceType,

SMS_R_SYSTEM.Name,

SMS_R_SYSTEM.SMSUniqueIdentifier,

SMS_R_SYSTEM.ResourceDomainORWorkgroup,

SMS_R_SYSTEM.Client

From SMS_R_System

Join SMS_SUMDeploymentAssetDetails D

On SMS_R_System.resourceID = D.resourceID

Where D.assignmentID = '<Assignment ID>' and D.StatusType = '<Status No>'

To 'Assignment ID', Open Configuration Manager Console > Monitoring > Overview > Deployment > Select and right click the deployment and tick the Assignment ID

Once you selected Assignment ID, a column will be added like below then copy the ID for your query:

And for machine status, below are the values for the respective status (Screenshot from Microsoft Document - SMS_SUMDeploymentAssetDetails Server WMI Class):

For example for Unknown machines:

Select SMS_R_SYSTEM.ResourceID,

SMS_R_SYSTEM.ResourceType,

SMS_R_SYSTEM.Name,

SMS_R_SYSTEM.SMSUniqueIdentifier,

SMS_R_SYSTEM.ResourceDomainORWorkgroup,

SMS_R_SYSTEM.Client

From SMS_R_System

Join SMS_SUMDeploymentAssetDetails D

On SMS_R_System.resourceID = D.resourceID

Where D.assignmentID = '16779116' and D.StatusType = '4'

Configuration Manager: How to force the machine to send DDR, Location Request, MP, and compliance report

To force any windows machine/computer's client agent to trigger sending DDR, Location Request, MP, and compliance report to Configuration Manager, please use the below command and run as administrator:

([wmiclass]'ROOT\ccm:SMS_Client').TriggerSchedule('{00000000-0000-0000-0000-000000000012}'); ([wmiclass]'ROOT\ccm:SMS_Client').TriggerSchedule('{00000000-0000-0000-0000-000000000024}'); ([wmiclass]'ROOT\ccm:SMS_Client').TriggerSchedule('{00000000-0000-0000-0000-000000000111}'); [wmi]"ROOT\ccm\invagt:InventoryActionStatus.InventoryActionID='{00000000-0000-0000-0000-000000000003}'" | remove-wmiobject ([wmiclass]'ROOT\ccm:SMS_Client').TriggerSchedule('{00000000-0000-0000-0000-000000000003}'); ([wmiclass]'ROOT\ccm:SMS_Client').TriggerSchedule('{00000000-0000-0000-0000-000000000021}'); ([wmiclass]'ROOT\ccm:SMS_Client').TriggerSchedule('{00000000-0000-0000-0000-000000000022}'); ([wmiclass]'ROOT\ccm:SMS_Client').TriggerSchedule('{00000000-0000-0000-0000-000000000040}') | out-null; "Eval. MachinePolicy"; [void]([wmiclass]'ROOT\ccm:SMS_Client').TriggerSchedule('{00000000-0000-0000-0000-000000000113}'); ([wmiclass]'ROOT\ccm:SMS_Client').TriggerSchedule('{00000000-0000-0000-0000-000000000108}'); "Update scan and evaluation" (New-Object -ComObject Microsoft.CCM.UpdatesStore).RefreshServerComplianceState()