lionofdezert

--DROP IF ALREADY EXISTS
IF EXISTS ( SELECT  1
            FROM    SYS.PROCEDURES
            WHERE   NAME = 'SYNC_Indexes' ) 
    DROP PROCEDURE SYNC_Indexes
GO
--CREATE PROCEDURE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

lionofdezert

--DROP IF ALREADY CREATED
IF EXISTS ( SELECT  1
            FROM    SYS.procedures
            WHERE   NAME = 'SYNC_CreatePrimaryKeys' ) 
    DROP PROC SYNC_CreatePrimaryKeys
GO

--CREATE PROCEDURE
SET ANSI_NULLS ON
GO

lionofdezert

--DROP IF ALREADY CREATED
IF EXISTS (SELECT 1 FROM sys.objects WHERE type = 'P' AND name = 'SYNC_CreateMissingColumns')
DROP PROC [dbo].[SYNC_CreateMissingColumns]
GO
--CREATE PROCEDURE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*

lionofdezert

--DROP IF ALREADY EXISTS
IF EXISTS ( SELECT  1
            FROM    SYS.procedures
            WHERE   NAME = 'SYNC_ColumnsDefaultValues' ) 
    DROP PROC SYNC_ColumnsDefaultValues

GO
--CREATE PROCEDURE
SET ANSI_NULLS ON
GO

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/Amna Asif for ConnectSQL
Purpose: To create DDL Change Log using Service Broker,
     for multiple databases on a single instance
*/
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = 'ConnectDBA')
	CREATE DATABASE ConnectDBA
GO
USE [ConnectDBA]
GO

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,