Skip to content

Instantly share code, notes, and snippets.

@lionofdezert
lionofdezert / TablesRelationshipTSQL.sql
Created July 31, 2012 16:27
SQL Server : Tables Relationship Report Through TSQL
-- 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
lionofdezert / DatabaseRestoreLog.sql
Created August 2, 2012 11:47
Database Restore Log
/************************
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
lionofdezert / AutoTrace.sql
Created August 30, 2012 17:19
Create a trace through TSQL and save it to a file
/***************************************************
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
lionofdezert / StopAutoTrace.sql
Created August 30, 2012 17:22
Stop Auto Trace
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
lionofdezert / JobsChangeStatusAlert.sql
Created September 12, 2012 08:21
Trigger to get jobs change status alert
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
lionofdezert / AllDBIndexesListWithIncludeColumns.sql
Created September 14, 2012 14:44
All Database Indexes List With Include Columns
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
lionofdezert / usp_stringpart.sql
Created October 17, 2012 19:45
Function takes a string and section number of string which is required and separator, which separates different sections of given string
--| 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
lionofdezert / sp_dbcmptlevel.sql
Created October 21, 2012 20:26
Change SQL Server System Stored Procedure to fix compatability level 80
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
lionofdezert / IndexesListWithUsedColsAndStats.sql
Created November 19, 2012 14:22
Get all indexes list with key columns and include columns as well as usage statistics
/*
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
lionofdezert / TablesInSPsWithoutIndexes.sql
Created November 29, 2012 14:12
Script to get list of stored procedures which contains tables but without non clusterd indexes
/*
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,