Execute one query in all SQL Server databases at once

We come across situations like searching for a table in all the databases in one server, or executing any kind of query to all the databases. In-fact sometimes we want to write queries for all the databases with small twist.

For example, I wanted to search for a table name if it used in any View/StoreProc definition within any of the database before I delete the table.

Following query does the same:

DECLARE @cmd VARCHAR(1000)
	,@search_string VARCHAR(200)


SET @cmd = 'SELECT DISTINCT ''?'', s.name AS Schema_Name, o.name AS Object_Name, o.type_desc 
			FROM [?].sys.sql_modules m INNER JOIN [?].sys.objects o ON m.object_id = o.object_id 
			INNER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id 
			WHERE m.definition Like ''%' + @search_string + '%'''

-- Uncomment the following if you have problems with your command and want to see the command
-- PRINT @cmd


--Run for every database on the server
EXEC sp_msforeachdb @cmd

Above kind of query can help you with :

a. Find a table name in complete SQL Server Databases

b. Prepare complete query which will be run on all the databases

c. Update something in all the databases at once

d. Example used with Dynamic query can help achieve many ad-hoc work done on SQL Server

List all the SSRS Report Subscription and Job linked with subscriptions.

SSRS Reports provide automation using Subscriptions. Specially Data Driven subscriptions provide more flexibility in automation.

As soon as a SSRS Report Subscription is created a SQL Agent Job is created in SSRS Report Database Server, which execute on schedule and make sure subscriptions are executed. Job names are UNIQUE IDENTIFIERs and not so friendly. To associate SSRS Report to Subscriptions to SQL Agent Job, following query can be used. And later we can update Job like enable/disable from backend OR get SQL query for subscription execution and use it in ad-hoc reequipment and do many more backend stuff.

Use the query and update it if needed for you. Let me know in comment if you need more help with this topic.

SELECT sj.[name] AS [JobName]
	,c.[Name] AS [ReportName]
	,C.Path AS ReportPath
	,Su.Description AS SubscriptionName
	,su.LastStatus
	,su.LastRunTime
	,rs.SUBSCRIPTIONID
	,sj.JOB_ID
	,c.ComponentID
	,CASE 
		WHEN su.DataSettings IS NULL
			AND EventType = 'TimedSubscription'
			THEN 'Standard'
		WHEN su.DataSettings IS NOT NULL
			AND EventType = 'TimedSubscription'
			THEN 'Data Driven'
		END SubscriptionType
FROM msdb..sysjobs AS sj
LEFT JOIN ReportServer..ReportSchedule AS rs ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128))
LEFT JOIN ReportServer..Subscriptions AS su ON rs.SubscriptionID = su.SubscriptionID
LEFT JOIN ReportServer..[Catalog] c ON su.Report_OID = c.ItemID
WHERE c.[Name] = 'DAILYETLEXECUTIONREPORT'
ORDER BY [ReportName]

Query helps you:

1. Find the Job name associated with each SSRS Report Subscription
2. Find Last Status of subscription execution
3. Find type of Subscription – Data Driven or Standard
4. Using Job information we can do many more things.