lionofdezert

--DROP IF ALREADY CREATED
IF EXISTS (SELECT 1 FROM sys.objects WHERE type = 'P' AND name = 'SYNC_CreateForignKeys')
DROP PROC [dbo].[SYNC_CreateForignKeys]
GO

--CREATE PROCEDURE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

lionofdezert

--DROP IF ALREADY CREATED
IF EXISTS (SELECT 1 FROM sys.objects WHERE type = 'P' AND name = 'SYNC_CreateMissingTables')
DROP PROC [dbo].[SYNC_CreateMissingTables]
GO

--CREATE PROCEDURE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

lionofdezert

/***********************************
Script By: Amna Asif
Purpose : To fix ALLOW_PAGE_LOCKS option on 
                 all indexes of all databases on a particular instance
***********************************/
USE MASTER
GO
SET QUOTED_IDENTIFIER ON
GO
SET ARITHABORT ON

lionofdezert

GO
USE [ConnectDBA]
GO
/*Alter table to add new column to hold client machine IP*/
ALTER TABLE [dbo].[DDLChangeLog] ADD [ClientMachineIP] [varchar](20) NULL

GO
--Create a stored procedure which will hold logic, how to get data from queue and insert to DDLChangeLog table.
CREATE PROCEDURE [ConnectDBA_Queue_EventNotificatier]
    WITH EXECUTE AS OWNER

lionofdezert

/*

Script By: Aasim Abdullah for http://connectsql.blogspot.com
Purpose: To get list of stored procedures which contains tables but without non clusterd indexes

*/


SELECT  *
FROM    ( SELECT    SCHEMA_NAME(schema_id) + '.' + NAME AS ProcedureName,

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]