List all the subscriptions scheduled in the Report Server

At times, we might need to pull the list of all the Subscriptions configured in the Report Server. In such cases the following script would be helpfull.

SELECT USR.UserName AS SubscriptionOwner
,SUB.ModifiedDate
,SUB.[Description]
,SUB.EventType
,SUB.DeliveryExtension
,SUB.LastStatus
,SUB.LastRunTime
,SCH.NextRunTime
,SCH.Name AS ScheduleName
,CAT.[Path] AS ReportPath
,CAT.[Description] AS ReportDescription
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR
ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.[Catalog] AS CAT
ON SUB.Report_OID = CAT.ItemID
INNER JOIN dbo.ReportSchedule AS RS
ON SUB.Report_OID = RS.ReportID
AND SUB.SubscriptionID = RS.SubscriptionID
INNER JOIN dbo.Schedule AS SCH
ON RS.ScheduleID = SCH.ScheduleID
ORDER BY USR.UserName
,CAT.[Path];

SQL Server Specific code – to Identify Locked table in SQL Server.

Select
OBJECT_Name(p.object_id) as TableName,
resource_Type,
resource_Description
From
Sys.dm_tran_locks l
Join Sys.partitions p on l.resource_associated_entity_id = p.hobt_id

List All columns of certain type in a specified database.

Select s.Name as ‘Schmea’,
ts.name as tableName,
schema_Name(t.schema_id) as DataTypesSchema,
t.Name as DataTypeName,
t.is_user_defined ,
t.is_assembly_type,
c.is_nullable,
c.max_length,
c.Precision,
c.scale
from Sys.columns AS c
Inner Join Sys.types as t on c.user_type_id=t.user_type_id
Inner join sys.tables ts on ts.object_id=c.object_id
Inner join sys.schemas s on s.schema_id =ts.schema_id
Order by s.name ,ts.Name,c.column_id