kristinaconley

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT * FROM LinkedServer.Database.dbo.Table

--Set back to original isolation level.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

kristinaconley

CREATE TABLE SubscriptionEmailList (
RowID INT IDENTITY (1,1)
,ReportName VARCHAR(200)
,SubscriptionDescription VARCHAR(200)
,JobID VARCHAR(40)
,TO VARCHAR(4000)
,CC VARCHAR(4000)
,BCC VARCHAR(4000)
,ReplyTo VARCHAR(4000)
,IncludeReport VARCHAR(5)

kristinaconley

DECLARE
       @table_name                       sysname              =      NULL   -- Specify table name
       , @partitioned_tables      bit                  =      NULL   -- NULL: all tables; 0: un-partitioned tables; 1: partitioned tables 
       , @aligned_indexes         bit                  =      NULL   -- NULL: all indexes; 0: un-aligned indexes; 1: aligned indexes       
       , @right_aligned           bit                  =      NULL   -- NULL: all partition functions; 0: left-aligned partition functions; 1: right-aligned partition functions
       , @index_types                    smallint      =      NULL   -- NULL: all indexes; 0: heaps; 1: clustered indexes; 2: heaps and clustered indexes; 3: non-clustered indexes
       , @debug                          bit                  =      0             -- 0: dynamic SQL statement is not printed; 1 dynamic SQL statement is printed
       SET NOCOUNT ON
       SET XACT_ABORT ON

kristinaconley

/*What is running?*/
SELECT
    program_name AS 'Program Name'
  ,count(*) AS 'Running Processes'
 FROM master..sysprocesses
 WHERE 
	ecid=0
 GROUP BY program_name 

 /*CPU processing*/

kristinaconley

UPDATE SN
   SET SN.PermanentRefcount = SN.PermanentRefcount - 1
FROM
   [ReportServerTempDB].dbo.SnapshotData AS SN
   INNER JOIN [ReportServerTempDB].dbo.ExecutionCache AS EC ON SN.SnapshotDataID = EC.SnapshotDataID
   INNER JOIN Catalog AS C ON EC.ReportID = C.ItemID
WHERE C.Path LIKE '%%'

DELETE EC
FROM

kristinaconley

SELECT avg_fragmentation_in_percent, index_type_desc, index_id 
FROM 
sys.dm_db_index_physical_stats  (DB_ID(), NULL, NULL, NULL , null) 

WHERE 
  OBJECT_NAME(OBJECT_ID)='Incident' 
	AND OBJECT_SCHEMA_NAME(OBJECT_ID)='dbo'

kristinaconley

SELECT 
  CAST(CONVERT(VARCHAR(10),GETDATE(),110) AS DATETIME) AS 'Today Start'
	,DATEADD(s,-1,CONVERT(VARCHAR(10),GETDATE()+1,101)) AS 'Today End'
	,CAST(DATEDIFF(dd,0,GETDATE()-1) AS DATETIME) AS 'Yesterday Start'
	,DATEADD(s,-1,DATEADD(dd, DATEDIFF(dd,0,GETDATE()),0)) AS 'Yesterday End'
	,CONVERT(VARCHAR(10), (DateAdd(DD, - 1 * (datepart(dw, GETDATE())), GETDATE() + 1)), 110) AS 'Week to Date Start'
	,DATEADD(s,-1,DATEADD(dd, DATEDIFF(dd,0,GETDATE()),0)) AS 'Week to Date End'
	,CONVERT(VARCHAR(10), (DateAdd(DD, - 1 * (6 + datepart(dw, GETDATE())), GETDATE())), 120) AS 'Prior Week Start'
	,CONVERT(VARCHAR(10), (DateAdd(DD, -1 * (datepart(dw, GETDATE())), GETDATE())), 120) + ' 23:59:59' AS 'Prior Week End'
	,CAST(DATEDIFF(dd,0,GETDATE()-7) AS DATETIME) AS 'Last 7 Days Start'

kristinaconley

USE [DatabaseName]
GO
/****** Object:  UserDefinedFunction [dbo].[udf_List2Table]    Script Date: 8/15/2013 2:09:47 PM ******/
SET QUOTED_IDENTIFIER ON
GO

ALTER  FUNCTION [dbo].[udf_List2Table]    
(    
@List VARCHAR(5000),    
@Delim CHAR    

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

WITH Packages
AS (
       SELECT DISTINCT CAST([parameter_value] AS VARCHAR(1000)) AS Parameter_value
                ,ei.package_name
                ,ei.project_name
                ,ei.folder_name
         FROM [SSISDB].[internal].[execution_parameter_values] v
         INNER JOIN SSISDB.internal.execution_info ei
              ON ei.execution_id = v.execution_id
         WHERE parameter_name Like '%Connection%'