Skip to content

Instantly share code, notes, and snippets.

@lionofdezert
lionofdezert / SYNC_Indexes.sql
Created Nov 29, 2013
Stored Procedure, to create missing indexes on tables values at target database by comparing both source and target databases.
View SYNC_Indexes.sql
--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
lionofdezert / SYNC_CreatePrimaryKeys.sql
Created Nov 29, 2013
Stored Procedure, to create missing primary keys at target database by comparing both source and target databases.
View SYNC_CreatePrimaryKeys.sql
--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
lionofdezert / SYNC_CreateMissingColumns.sql
Created Nov 29, 2013
Stored Procedure, to create missing columns at target database by comparing both source and target databases.
View SYNC_CreateMissingColumns.sql
--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
lionofdezert / SYNC_ColumnsDefaultValues.sql
Created Nov 29, 2013
Stored Procedure, to create missing columns default values at target database by comparing both source and target databases.
View SYNC_ColumnsDefaultValues.sql
--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
lionofdezert / SYNC_CreateForignKeys.sql
Created Nov 29, 2013
Stored Procedure, to create missing foreign keys at target database after comparing both source and target databases.
View SYNC_CreateForignKeys.sql
--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
lionofdezert / Sync_CreateMissingTables.sql
Last active Dec 27, 2015
Script to find and create missing tables to synchronize two databases
View Sync_CreateMissingTables.sql
--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
lionofdezert / ALLOW_PAGE_LOCKS_FIX.sql
Last active Dec 14, 2015
Script to fix ALLOW_PAGE_LOCKS option on all indexes of all databases on a particular instance
View ALLOW_PAGE_LOCKS_FIX.sql
/***********************************
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
lionofdezert / DDLChangeLogServiceBrokerClientIP.sql
Created Dec 27, 2012
DDL Changes Log additional changes in script to get client IP
View DDLChangeLogServiceBrokerClientIP.sql
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
lionofdezert / DDLChangeLogServiceBroker..sql
Last active Jul 6, 2017
To create DDL Change Log using Service Broker, for multiple databases on a single instance
View DDLChangeLogServiceBroker..sql
/*
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
lionofdezert / TablesInSPsWithoutIndexes.sql
Created Nov 29, 2012
Script to get list of stored procedures which contains tables but without non clusterd indexes
View TablesInSPsWithoutIndexes.sql
/*
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,