lionofdezert

USE master
GO
-- Create table to hold valid IP values
CREATE TABLE ValidIPAddress (IP NVARCHAR(15)
CONSTRAINT PK_ValidAddress PRIMARY KEY)


-- Declare local machine as valid one
INSERT INTO ValidIPAddress
SELECT '<local machine>'

lionofdezert

/*
 Script to send an alert through mail, with information that how many drive 
 space is required from next databases growth on a specific instance and how many
 space is available. 



 Script By: Amna Asif for ConnectSQL.blogspot.com
 */

lionofdezert

-- Tables Relationship Script
-- Script By: Syed Muhammad Yasir for http://connectsql.blogspot.com
-- Updated August 1, 2012
--

SELECT  CASE WHEN a.parent_object_id IS NULL
             THEN parent.name + '-1--*-' + child.name
             ELSE parent.name + '-1--1-' + child.name
        END AS TablesWithRelations

lionofdezert

/************************
Script Purpose: To Keep Database Restore Log
Script By : Aasim Abdullah for https://connectsql.blogspot.com
************************/

USE master
GO
CREATE TABLE DatabaseRestoreLog 
(DatabaseName VARCHAR(50), RestoreDate DATETIME, RestoredFrom VARCHAR(500))

lionofdezert

USE AdventureWorks

GO

--============== Supporting function dbo.udfGetFullQualName
IF OBJECT_ID('dbo.udfGetFullQualName') IS NOT NULL 
    DROP FUNCTION dbo.udfGetFullQualName
GO
CREATE FUNCTION dbo.udfGetFullQualName ( @ObjectId INTEGER )

lionofdezert

-- Table variable to hold intermediate data
DECLARE @ReportSQLErrorLogs TABLE
    (
      [log_date] [datetime] NULL,
      [processinfo] [varchar](255) NULL,
      [processtext] [text] NULL
    )

DECLARE @NumErrorLogs INT,
    @CurrentLogNum INT

lionofdezert

/***************************************************
 Purpose: To create a trace and saving it to a file
 Created by: AASIM ABDULLAH  
 For http://connectsql.blogspot.com  
 Date: 08/29/2012       
****************************************************/
 
-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT

lionofdezert

DECLARE @TraceID INT
SELECT TOP 1 @TraceID = id FROM sys.traces
WHERE path LIKE '%_AutoTrace%'


exec sp_trace_setstatus @TraceID, 0 --Stop trace
exec sp_trace_setstatus @TraceID, 2 --Close trace

lionofdezert

/******************************************
Script By: Aasim Abdullah 
For : http://connectsql.blogspot.com
Purpose: To detect long running sessions,
			send complete information through mail about such sessions
			and killing session, which are acceding given limit of execution time.
******************************************/

---BusyProcess Detection
SET NOCOUNT ON

lionofdezert

USE [msdb]
GO
/* ======================================================
Script By: Aasim Abdullah @http://connectsql.blogspot.com
Script For: Create trigger on msdb.dbo.sysjobs table, 
				to detect any change in job status by any 
				user and mail it to DB team 
-- =================================================== */
CREATE TRIGGER [dbo].[JobStatusAlert]
   ON   [dbo].[sysjobs]