lionofdezert

/*
Script By: Aasim Abdullah for http://connectsql.blogspot.com
Get all indexes list with key columns and include columns as well as usage statistics
*/


 SELECT  '[' + Sch.name + '].[' + Tab.[name] + ']' AS TableName,
		Ind.type_desc,
        Ind.[name] AS IndexName,
        SUBSTRING(( SELECT  ', ' + AC.name

lionofdezert

USE mssqlsystemresource
GO
alter procedure sys.sp_dbcmptlevel     
 @dbname sysname = NULL,     -- database name to change  
 @new_cmptlevel tinyint = NULL OUTPUT -- the new compatibility level to change to  
as  
 set nocount    on  
  
 declare @exec_stmt nvarchar(max)  
 declare @returncode int  

lionofdezert

--| Create By: Aasim Abdullah
--| Description: Function takes a string and section number of string which
--|              is required and separator, which separates different
--|              sections of given string
--| How To Use: SELECT dbo.usp_stringpart('First,Second,Third,Forth,Fifth',3,',')

CREATE FUNCTION dbo.usp_stringpart
    (
      @InputString NVARCHAR(1000),

lionofdezert

SELECT  '[' + Sch.name + '].[' + Tab.[name] + ']' AS TableName,
        Ind.[name] AS IndexName,
        SUBSTRING(( SELECT  ', ' + AC.name
                    FROM    sys.[tables] AS T
                            INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
                            INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
                                                                 AND I.[index_id] = IC.[index_id]
                            INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id]
                                                               AND IC.[column_id] = AC.[column_id]
                    WHERE   Ind.[object_id] = I.[object_id]

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]

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

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

/***************************************************
 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

-- 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

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 )