kristinaconley

USE ReportServer
 
SELECT
  C.Name AS ReportName
  ,CASE 
  	WHEN NEXT_RUN_DATE > 0 
				THEN DATEADD(N,(NEXT_RUN_TIME%10000)/100
            ,DATEADD(HH,NEXT_RUN_TIME/10000
            ,CONVERT(DATETIME,CONVERT(VARCHAR(8),NEXT_RUN_DATE),112)))  
		ELSE CONVERT(DATETIME,CONVERT(VARCHAR(8),'19000101'),112) 

kristinaconley

/*Execute SSRS Subscription Manually*/
USE ReportServer
SELECT
      S.ScheduleID AS SQLAgent_Job_Name
      ,SUB.Description AS Sub_Desc
      ,SUB.DeliveryExtension AS Sub_Del_Extension
      ,C.Name AS ReportName
      ,C.Path AS ReportPath
FROM ReportSchedule RS (NOLOCK)
      INNER JOIN Schedule S (NOLOCK) ON (RS.ScheduleID = S.ScheduleID) 

kristinaconley

USE [ReportServer];  -- You may change the database name. 
GO 
 
SELECT USR.UserName AS SubscriptionOwner 
      ,SUB.ModifiedDate 
      ,SUB.[Description] 
      ,SUB.EventType 
      ,SUB.DeliveryExtension 
      ,SUB.LastStatus 
      ,SUB.LastRunTime 

kristinaconley

CREATE FUNCTION [dbo].[CamelCase]
    (
     @Str     VARCHAR(8000)
    )
RETURNS VARCHAR(8000)
AS
     BEGIN
          DECLARE @Result     VARCHAR(2000)
          SET @Str = LOWER(@Str) + ' '
          SET @Result = ''

kristinaconley

SELECT 
  'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas 
  ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'OldSchemaName'
  AND (DbObjects.Type IN ('U', 'P', 'V'))

kristinaconley

DECLARE @DBInfo TABLE  
( ServerName VARCHAR(100),  
DatabaseName VARCHAR(100),
DbSize VARCHAR(100),
SpaceUsed VARCHAR(100),
SpacePercentFree VARCHAR(100),
LogSize VARCHAR(100)
)  

DECLARE @command VARCHAR(5000)  

kristinaconley

SELECT 
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
    msdb.dbo.backupset.database_name, 
    msdb.dbo.backupset.backup_start_date, 
    msdb.dbo.backupset.backup_finish_date, 
    msdb.dbo.backupset.expiration_date, 
    CASE msdb..backupset.type 
    WHEN 'D' THEN 'Database' 
    WHEN 'L' THEN 'Log' 
    END AS backup_type, 

kristinaconley

exec sp_MSForEachDB  
'
USE ?
SELECT DISTINCT 
       o.name AS Object_Name,
       o.type_desc,
       m.definition
FROM sys.sql_modules m 
INNER JOIN sys.objects o 
     ON m.object_id = o.object_id

kristinaconley

USE MSDB
GO
SELECT
sj.job_id as JobId,
sj.name as JobName,
sjs.step_name as StepName,
sjs.Command as Command
FROM sysjobs sj
INNER JOIN sysjobsteps sjs
ON(sj.job_id = sjs.job_id)

kristinaconley

WITH XMLNAMESPACES 
    -- XML namespace def must be the first in with clause. 
    (
    DEFAULT 
    'http://schemas.microsoft.com/sqlserver/reporting/2006/03/reportdatasource'
    ,
    'http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' 
    AS rd
    )
    ,SDS