THE CAUSE OF EVERY DEADLOCK IN SQL SERVER

THE CAUSE OF EVERY DEADLOCK IN SQL SERVER
Posted by sqlrockstar Employee in Geek Speak on 19-Jun-2016 01:06:00

https://thwack.solarwinds.com/community/solarwinds-community/geek-speak/blog/2016/06/18/the-cause-of-every-deadlock-in-sql-server#start=25

Advertisements

Compare two databases for objects differences

CREATE PROC sp_CompareDb(
@SourceDB SYSNAME,
@TargetDb SYSNAME
)
AS

/*
DECLARE @SourceDB SYSNAME=’DB1′,
@TargetDb SYSNAME=’DB2′
*/
SET nocount ON
SET ansi_warnings ON
SET ansi_nulls ON

DECLARE @sqlStr VARCHAR(8000)
SET @SourceDB= Rtrim(Ltrim(@SourceDB))
IF DB_ID(@SourceDB) IS NULL
BEGIN
PRINT ‘Error: Unable to find the database ‘+ @SourceDB +’!!!’
RETURN
END

SET @TargetDb= Rtrim(Ltrim(@TargetDb))
IF DB_ID(@TargetDb) IS NULL
BEGIN
PRINT ‘Error: Unable to find the database ‘+ @TargetDb +’!!!’
RETURN
END

PRINT Replicate(‘-‘, Len(@SourceDB) + Len(@TargetDb) + 25)
PRINT ‘Comparing databases ‘ + @SourceDB + ‘ and ‘ + @TargetDb
PRINT Replicate(‘-‘, Len(@SourceDB) + Len(@TargetDb) + 25)
—————————————————————————————–
— Create temp tables needed to hold the db structure
—————————————————————————————–

IF OBJECT_ID(‘TEMPDB..#TABLIST_SOURCE’)IS NOT NULL
DROP TABLE #TABLIST_SOURCE
IF OBJECT_ID(‘TEMPDB..#TABLIST_TARGET’)IS NOT NULL
DROP TABLE #TABLIST_TARGET
IF OBJECT_ID(‘TEMPDB..#IDXLIST_SOURCE’)IS NOT NULL
DROP TABLE #IDXLIST_SOURCE
IF OBJECT_ID(‘TEMPDB..#IDXLIST_TARGET’)IS NOT NULL
DROP TABLE #IDXLIST_TARGET
IF OBJECT_ID(‘TEMPDB..#FKLIST_SOURCE’)IS NOT NULL
DROP TABLE #FKLIST_SOURCE
IF OBJECT_ID(‘TEMPDB..#FKLIST_TARGET’)IS NOT NULL
DROP TABLE #FKLIST_TARGET
IF OBJECT_ID(‘TEMPDB..#TAB_RESULTS’)IS NOT NULL
DROP TABLE #TAB_RESULTS
IF OBJECT_ID(‘TEMPDB..#IDX_RESULTS’)IS NOT NULL
DROP TABLE #IDX_RESULTS
IF OBJECT_ID(‘TEMPDB..#FK_RESULTS’)IS NOT NULL
DROP TABLE #FK_RESULTS

CREATE TABLE #TABLIST_SOURCE
(
ID INT IDENTITY(1,1),
DATABASENAME sysname,
TABLENAME SYSNAME ,
COLUMNNAME SYSNAME,
DATATYPE SYSNAME,
NULLABLE VARCHAR(15)
)

CREATE TABLE #TABLIST_TARGET
(
ID INT IDENTITY(1,1),
DATABASENAME sysname,
TABLENAME SYSNAME ,
COLUMNNAME SYSNAME,
DATATYPE SYSNAME,
NULLABLE VARCHAR(15)
)

CREATE TABLE #IDXLIST_SOURCE
(
ID INT IDENTITY(1,1),
DATABASENAME sysname,
TABLE_NAME SYSNAME,
IDX_NAME SYSNAME ,
IDX_TYPE VARCHAR(20),
IS_PRIMARY_KEY VARCHAR(10),
IS_UNIQUE VARCHAR(10),
IDX_COLUMNS VARCHAR(1000),
IDX_INCLUDED_COLUMNS VARCHAR(1000)
);

CREATE TABLE #IDXLIST_TARGET
(
ID INT IDENTITY(1,1),
DATABASENAME sysname,
TABLE_NAME SYSNAME,
IDX_NAME SYSNAME ,
IDX_TYPE VARCHAR(20),
IS_PRIMARY_KEY VARCHAR(10),
IS_UNIQUE VARCHAR(10),
IDX_COLUMNS VARCHAR(1000),
IDX_INCLUDED_COLUMNS VARCHAR(1000)
);

CREATE TABLE #FKLIST_SOURCE
(
ID INT IDENTITY(1,1),
DATABASENAME sysname,
FK_NAME SYSNAME,
FK_TABLE sysname,
FK_COLUMNS varchar(1000),
PK_TABLE sysname,
PK_COLUMNS varchar(1000)
);

CREATE TABLE #FKLIST_TARGET
(
ID INT IDENTITY(1,1),
DATABASENAME sysname,
FK_NAME SYSNAME,
FK_TABLE sysname,
FK_COLUMNS varchar(1000),
PK_TABLE sysname,
PK_COLUMNS varchar(1000)
);

CREATE TABLE #TAB_RESULTS
(
ID INT IDENTITY(1,1),
DATABASENAME sysname,
TABLENAME SYSNAME ,
COLUMNNAME SYSNAME,
DATATYPE SYSNAME,
NULLABLE VARCHAR(15),
REASON VArchar(150)
);

CREATE TABLE #IDX_RESULTS
(
ID INT IDENTITY(1,1),
DATABASENAME sysname,
TABLE_NAME SYSNAME,
IDX_NAME SYSNAME ,
IDX_TYPE VARCHAR(20),
IS_PRIMARY_KEY VARCHAR(10),
IS_UNIQUE VARCHAR(10),
IDX_COLUMNS VARCHAR(1000),
IDX_INCLUDED_COLUMNS VARCHAR(1000),
REASON Varchar(150)
);

CREATE TABLE #FK_RESULTS
(
ID INT IDENTITY(1,1),
DATABASENAME sysname,
FK_NAME SYSNAME,
FK_TABLE sysname,
FK_COLUMNS varchar(1000),
PK_TABLE sysname,
PK_COLUMNS varchar(1000),
REASON VArchar(150)
);

PRINT ‘Getting table and column list!’;
PRINT Replicate(‘-‘, Len(@SourceDB) + Len(@TargetDb) + 25);

BEGIN
INSERT INTO #TABLIST_SOURCE(DATABASENAME, TABLENAME,COLUMNNAME,DATATYPE,NULLABLE)
EXEC(‘SELECT ”’+@SourceDB +”’, T.TABLE_NAME TABLENAME,
C.COLUMN_NAME COLUMNNAME,
TY.name + case when TY.name IN (”char”,”varchar”,”nvarchar”) THEN
”(”+CASE WHEN C.CHARACTER_MAXIMUM_LENGTH>0 THEN CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ELSE ”max”END+”)”
ELSE
””
END
DATATYPE,
CASE WHEN C.is_nullable=”NO” THEN
”NOT NULL”
ELSE
”NULL”
END NULLABLE
FROM ‘+@SourceDB+’.INFORMATION_SCHEMA.TABLES T
INNER JOIN ‘+@SourceDB+’.INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME=C.TABLE_NAME
and T.TABLE_CATALOG=C.TABLE_CATALOG
and T.TABLE_SCHEMA=C.TABLE_SCHEMA
INNER JOIN ‘+@SourceDB+’.sys.types TY
ON C.DATA_TYPE =TY.name
ORDER BY TABLENAME, COLUMNNAME,C.ORDINAL_POSITION’);

INSERT INTO #TABLIST_TARGET(DATABASENAME, TABLENAME,COLUMNNAME,DATATYPE,NULLABLE)
EXEC(‘SELECT ”’+@TargetDB +”’, T.TABLE_NAME TABLENAME,
C.COLUMN_NAME COLUMNNAME,
TY.name + case when TY.name IN (”char”,”varchar”,”nvarchar”) THEN
”(”+CASE WHEN C.CHARACTER_MAXIMUM_LENGTH>0 THEN CAST(C.CHARACTER_MAXIMUM_LENGTH AS VARCHAR) ELSE ”max”END+”)”
ELSE
””
END
DATATYPE,
CASE WHEN C.is_nullable=”NO” THEN
”NOT NULL”
ELSE
”NULL”
END NULLABLE
FROM ‘+@TargetDB+’.INFORMATION_SCHEMA.TABLES T
INNER JOIN ‘+@TargetDB+’.INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME=C.TABLE_NAME
and T.TABLE_CATALOG=C.TABLE_CATALOG
and T.TABLE_SCHEMA=C.TABLE_SCHEMA
INNER JOIN ‘+@TargetDB+’.sys.types TY
ON C.DATA_TYPE =TY.name
ORDER BY TABLENAME, COLUMNNAME,C.ORDINAL_POSITION’);

PRINT ‘Getting index list!’;
PRINT Replicate(‘-‘, Len(@SourceDB) + Len(@TargetDb) + 25);

INSERT INTO #IDXLIST_SOURCE(DATABASENAME, TABLE_NAME,IDX_NAME,IDX_TYPE,IS_PRIMARY_KEY,IS_UNIQUE,IDX_COLUMNS,IDX_INCLUDED_COLUMNS)
EXEC (‘WITH CTE AS (
SELECT ic.index_id + ic.object_id AS IndexId,t.name AS TableName
,i.name AS IndexName
,case when ic.is_included_column =0 then
c.name end AS ColumnName
,case when ic.is_included_column =1 then
c.name end AS IncludedColumn
,i.type_desc
,i.is_primary_key,i.is_unique
FROM ‘+@SourceDB+’.sys.indexes i
INNER JOIN ‘+@SourceDB+’.sys.index_columns ic
ON i.index_id = ic.index_id
AND i.object_id = ic.object_id
INNER JOIN ‘+@SourceDB+’.sys.columns c
ON ic.column_id = c.column_id
AND i.object_id = c.object_id
INNER JOIN ‘+@SourceDB+’.sys.tables t
ON i.object_id = t.object_id
)
SELECT ”’+@SourceDB+”’,c.TableName TABLE_NAME,c.IndexName INDEX_NAME,c.type_desc INDEX_TYPE ,c.is_primary_key IS_PRIMARY_KEY,c.is_unique IS_UNIQUE
,STUFF( ( SELECT ”,”+ a.ColumnName FROM CTE a WHERE c.IndexId = a.IndexId FOR XML PATH(””)),1 ,1, ””) AS COLUMNS
,STUFF( ( SELECT ”,”+ a.IncludedColumn FROM CTE a WHERE c.IndexId = a.IndexId FOR XML PATH(””)),1 ,1, ””) AS INCLUDED_COLUMNS
FROM CTE c
GROUP BY c.IndexId,c.TableName,c.IndexName,c.type_desc,c.is_primary_key,c.is_unique
ORDER BY c.TableName ASC,c.is_primary_key DESC; ‘ );

INSERT INTO #IDXLIST_TARGET(DATABASENAME,TABLE_NAME,IDX_NAME,IDX_TYPE,IS_PRIMARY_KEY,IS_UNIQUE,IDX_COLUMNS,IDX_INCLUDED_COLUMNS)
EXEC (‘WITH CTE AS (
SELECT ic.index_id + ic.object_id AS IndexId,t.name AS TableName
,i.name AS IndexName
,case when ic.is_included_column =0 then
c.name end AS ColumnName
,case when ic.is_included_column =1 then
c.name end AS IncludedColumn
,i.type_desc
,i.is_primary_key,i.is_unique
FROM ‘+@TargetDB+’.sys.indexes i
INNER JOIN ‘+@TargetDB+’.sys.index_columns ic
ON i.index_id = ic.index_id
AND i.object_id = ic.object_id
INNER JOIN ‘+@TargetDB+’.sys.columns c
ON ic.column_id = c.column_id
AND i.object_id = c.object_id
INNER JOIN ‘+@TargetDB+’.sys.tables t
ON i.object_id = t.object_id
)
SELECT ”’+@TargetDB+”’,c.TableName,c.IndexName,c.type_desc,c.is_primary_key,c.is_unique
,STUFF( ( SELECT ”,”+ a.ColumnName FROM CTE a WHERE c.IndexId = a.IndexId FOR XML PATH(””)),1 ,1, ””) AS Columns
,STUFF( ( SELECT ”,”+ a.IncludedColumn FROM CTE a WHERE c.IndexId = a.IndexId FOR XML PATH(””)),1 ,1, ””) AS IncludedColumns
FROM CTE c
GROUP BY c.IndexId,c.TableName,c.IndexName,c.type_desc,c.is_primary_key,c.is_unique
ORDER BY c.TableName ASC,c.is_primary_key DESC; ‘);

PRINT ‘Getting foreign key list!’;
PRINT Replicate(‘-‘, Len(@SourceDB) + Len(@TargetDb) + 25);

INSERT INTO #FKLIST_SOURCE(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS)
EXEC (‘With CTE
AS
(select OBJECT_NAME(FK.parent_object_id,db_id(”’+@TargetDB+”’)) PK_TABLE,
C1.name PK_COLUMN,
object_name(FK.referenced_object_id,db_id(”’+@TargetDB+”’))FK_TABLE,
C2.name FK_COLUMN,
FK.name FK_NAME
from
‘+@SourceDB+’.sys.foreign_keys FK
inner join
‘+@SourceDB+’.sys.foreign_key_columns FKC
on FK.object_id=FKC.constraint_object_id
inner join
‘+@SourceDB+’.sys.columns C1
on FKC.parent_column_id=C1.column_id
and FKC.parent_object_id=C1.object_id
inner join
‘+@SourceDB+’.sys.columns C2
on FKC.referenced_column_id=C2.column_id
and FKC.referenced_object_id=C2.object_id
)
SELECT ”’+@SourceDB+”’,C.FK_NAME,
C.FK_TABLE, STUFF( ( SELECT ”,”+ A.FK_COLUMN FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.FK_TABLE=a.FK_TABLE FOR XML PATH(””)),1 ,1, ””) AS FK_COLUMNS,
C.PK_TABLE,
STUFF( ( SELECT ”,”+ A.PK_Column FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.PK_TABLE=a.PK_TABLE FOR XML PATH(””)),1 ,1, ””) AS PK_COLUMNS
FROM CTE C
group by C.FK_NAME,
C.FK_TABLE,
C.PK_TABLE’)

INSERT INTO #FKLIST_TARGET(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS)
EXEC(‘
With CTE
AS
(select OBJECT_NAME(FK.parent_object_id,db_id(”’+@TargetDB+”’)) PK_TABLE,
C1.name PK_COLUMN,
object_name(FK.referenced_object_id,db_id(”’+@TargetDB+”’))FK_TABLE,
C2.name FK_COLUMN,
FK.name FK_NAME
from
‘+@TargetDB+’.sys.foreign_keys FK
inner join
‘+@TargetDB+’.sys.foreign_key_columns FKC
on FK.object_id=FKC.constraint_object_id
inner join
‘+@TargetDB+’.sys.columns C1
on FKC.parent_column_id=C1.column_id
and FKC.parent_object_id=C1.object_id
inner join
‘+@TargetDB+’.sys.columns C2
on FKC.referenced_column_id=C2.column_id
and FKC.referenced_object_id=C2.object_id
)
SELECT ”’+@TargetDB+”’,C.FK_NAME,
C.FK_TABLE, STUFF( ( SELECT ”,”+ A.FK_COLUMN FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.FK_TABLE=a.FK_TABLE FOR XML PATH(””)),1 ,1, ””) AS FK_COLUMNS,
C.PK_TABLE,
STUFF( ( SELECT ”,”+ A.PK_Column FROM CTE a WHERE c.FK_NAME = a.FK_NAME and C.PK_TABLE=a.PK_TABLE FOR XML PATH(””)),1 ,1, ””) AS PK_COLUMNS
FROM CTE C
group by C.FK_NAME,
C.FK_TABLE,
C.PK_TABLE’)
END;

PRINT ‘Print column mismatches!’;
PRINT Replicate(‘-‘, Len(@SourceDB) + Len(@TargetDb) + 25);

INSERT INTO #TAB_RESULTS(DATABASENAME, TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON)
SELECT @SourceDB AS DATABASENAME,TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON FROM
(SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_SOURCE
EXCEPT
SELECT TS.TABLENAME,TS.COLUMNNAME,TS.DATATYPE,TS.NULLABLE FROM #TABLIST_SOURCE TS
INNER JOIN
#TABLIST_TARGET TT
ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME) TAB_NONMATCH
CROSS JOIN (SELECT ‘Missing Column’ As Reason)Tab2
UNION ALL
SELECT @TargetDb as DATABASENAME,TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON FROM
(SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_TARGET
EXCEPT
SELECT TT.TABLENAME,TT.COLUMNNAME,TT.DATATYPE,TT.NULLABLE FROM #TABLIST_TARGET TT
INNER JOIN
#TABLIST_SOURCE TS
ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME) TAB_MATCH
CROSS JOIN (SELECT ‘Missing column ‘ As Reason)Tab2

–NON MATCHING COLUMNS
INSERT INTO #TAB_RESULTS(DATABASENAME,TABLENAME,COLUMNNAME,DATATYPE,NULLABLE,REASON)
SELECT
@SourceDB as DATABASENAME,
TABLENAME,
COLUMNNAME,
DATATYPE,
NULLABLE,
REASON
FROM
(SELECT * FROM
(SELECT TS.TABLENAME,TS.COLUMNNAME,TS.DATATYPE,TS.NULLABLE FROM #TABLIST_SOURCE TS
INNER JOIN
#TABLIST_TARGET TT ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME)T
EXCEPT
(SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_SOURCE
INTERSECT
SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_TARGET))TT1
CROSS JOIN (SELECT ‘Definition not matching’AS REASON) t

UNION ALL

SELECT @TargetDb as DATABASENAME,
TABLENAME,
COLUMNNAME,
DATATYPE,
NULLABLE,
REASON
FROM(
SELECT * FROM
(SELECT TT.TABLENAME,TT.COLUMNNAME,TT.DATATYPE,TT.NULLABLE FROM #TABLIST_TARGET TT
INNER JOIN
#TABLIST_SOURCE TS ON TS.TABLENAME=TT.TABLENAME AND TS.COLUMNNAME=TT.COLUMNNAME)T
EXCEPT
(SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_TARGET
INTERSECT
SELECT TABLENAME,COLUMNNAME,DATATYPE,NULLABLE FROM #TABLIST_SOURCE))TAB_NONMATCH
CROSS JOIN (SELECT ‘Definition not matching’ AS REASON)T;

PRINT ‘Print index mismatches!’;
PRINT Replicate(‘-‘, Len(@SourceDB) + Len(@TargetDb) + 25);

INSERT INTO #IDX_RESULTS(DATABASENAME, TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON)
SELECT @SourceDB AS DATABASENAME, TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON FROM
(SELECT TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_SOURCE
EXCEPT
SELECT TS.TABLE_NAME,TS.IDX_NAME, TS.IDX_COLUMNS,TS.IDX_INCLUDED_COLUMNS,TS.IS_PRIMARY_KEY,TS.IS_UNIQUE FROM #IDXLIST_SOURCE TS
INNER JOIN
#IDXLIST_TARGET TT
ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME) TAB_NONMATCH
CROSS JOIN (SELECT ‘Missing Index n’ As Reason)Tab2
UNION ALL
SELECT @TargetDb as DATABASENAME, TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON FROM
(SELECT TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_TARGET
EXCEPT
SELECT TT.TABLE_NAME,TT.IDX_NAME,TT.IDX_COLUMNS,TT.IDX_INCLUDED_COLUMNS,TT.IS_PRIMARY_KEY,TT.IS_UNIQUE FROM #IDXLIST_TARGET TT
INNER JOIN
#IDXLIST_SOURCE TS
ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME) TAB_MATCH
CROSS JOIN (SELECT ‘Missing index ‘ As Reason)Tab2

–NON MATCHING INDEX
INSERT INTO #IDX_RESULTS(DATABASENAME,TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE,REASON)
SELECT
@SourceDB as DATABASENAME,
TABLE_NAME,
IDX_NAME,
IDX_COLUMNS,
IDX_INCLUDED_COLUMNS,
IS_PRIMARY_KEY,
IS_UNIQUE,
REASON
FROM
(SELECT * FROM
(SELECT TS.TABLE_NAME,
TS.IDX_NAME,
TS.IDX_COLUMNS,
TS.IDX_INCLUDED_COLUMNS,
TS.IS_PRIMARY_KEY,
TS.IS_UNIQUE
FROM #IDXLIST_SOURCE TS
INNER JOIN
#IDXLIST_TARGET TT ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME)T
EXCEPT
(SELECT TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_SOURCE
INTERSECT
SELECT TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_TARGET))TT1
CROSS JOIN (SELECT ‘Definition not matching’ AS REASON) t

UNION ALL

SELECT @TargetDb as DATABASENAME,
TABLE_NAME,
IDX_NAME,
IDX_COLUMNS,
IDX_INCLUDED_COLUMNS,
IS_PRIMARY_KEY,
IS_UNIQUE,
REASON
FROM(
SELECT * FROM
(SELECT TT.TABLE_NAME,
TT.IDX_NAME,
TT.IDX_COLUMNS,
TT.IDX_INCLUDED_COLUMNS,
TT.IS_PRIMARY_KEY,
TT.IS_UNIQUE FROM #IDXLIST_TARGET TT
INNER JOIN
#IDXLIST_SOURCE TS ON TS.TABLE_NAME=TT.TABLE_NAME AND TS.IDX_NAME=TT.IDX_NAME)T
EXCEPT
(SELECT TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_TARGET
INTERSECT
SELECT TABLE_NAME,IDX_NAME,IDX_COLUMNS,IDX_INCLUDED_COLUMNS,IS_PRIMARY_KEY,IS_UNIQUE FROM #IDXLIST_SOURCE))TAB_NONMATCH
CROSS JOIN (SELECT ‘Definition not matching’ AS REASON)T;

PRINT ‘Print key mismatches!’;
PRINT Replicate(‘-‘, Len(@SourceDB) + Len(@TargetDb) + 25);

INSERT INTO #FK_RESULTS(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON)
SELECT @SourceDB AS DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON
FROM
(SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_SOURCE
EXCEPT
SELECT TS.FK_NAME,TS.FK_TABLE,TS.FK_COLUMNS,TS.PK_TABLE,TS.PK_COLUMNS FROM #FKLIST_SOURCE TS
INNER JOIN
#FKLIST_TARGET TT
ON TS.FK_NAME=TT.FK_NAME) TAB_NONMATCH
CROSS JOIN (SELECT ‘Missing Index n’ As Reason)Tab2

UNION ALL

SELECT @TargetDb as DATABASENAME,FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON FROM
(SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_TARGET
EXCEPT
SELECT TT.FK_NAME,TT.FK_TABLE,TT.FK_COLUMNS,TT.PK_TABLE,TT.PK_COLUMNS FROM #FKLIST_TARGET TT
INNER JOIN
#FKLIST_SOURCE TS
ON TS.FK_NAME=TT.FK_NAME) TAB_MATCH
CROSS JOIN (SELECT ‘Missing key’ As Reason)Tab2

–NON MATCHING Keys
INSERT INTO #FK_RESULTS(DATABASENAME, FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS,REASON)
SELECT
@SourceDB as DATABASENAME,
FK_NAME,
FK_TABLE,
FK_COLUMNS,
PK_TABLE,
PK_COLUMNS,
REASON
FROM
(SELECT * FROM
(SELECT TS.FK_NAME,
TS.FK_TABLE,
TS.FK_COLUMNS,
TS.PK_TABLE,
TS.PK_COLUMNS
FROM #FKLIST_SOURCE TS
INNER JOIN
#FKLIST_TARGET TT ON TS.FK_NAME=TT.FK_NAME)T
EXCEPT
(SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_SOURCE
INTERSECT
SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_TARGET))TT1
CROSS JOIN (SELECT ‘Definition not matching’ AS REASON) t

UNION ALL

SELECT @TargetDb as DATABASENAME,
FK_NAME,
FK_TABLE,
FK_COLUMNS,
PK_TABLE,
PK_COLUMNS,
REASON
FROM(
SELECT * FROM
(SELECT TT.FK_NAME,
TT.FK_TABLE,
TT.FK_COLUMNS,
TT.PK_TABLE,
TT.PK_COLUMNS FROM #FKLIST_TARGET TT
INNER JOIN
#FKLIST_SOURCE TS ON TS.FK_NAME=TT.FK_NAME)T
EXCEPT
(SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_TARGET
INTERSECT
SELECT FK_NAME,FK_TABLE,FK_COLUMNS,PK_TABLE,PK_COLUMNS FROM #FKLIST_SOURCE))TAB_NONMATCH
CROSS JOIN (SELECT ‘Definition not matching’ AS REASON)T;

–Print Final Results

SELECT * FROM #TAB_RESULTS
SELECT * FROM #IDX_RESULTS
SELECT * FROM #FK_RESULTS

https://gallery.technet.microsoft.com/scriptcenter/Compare-two-databases-for-b44e7afa

select all and remove all with chosen js

$(“select”).on(“chosen:showing_dropdown”, function(evnt, params) {
var chosen = params.chosen,
$dropdown = $(chosen.dropdown),
$field = $(chosen.form_field);
if( !chosen.__customButtonsInitilized ) {
chosen.__customButtonsInitilized = true;
var contained = function( el ) {
var container = document.createElement(“div”);
container.appendChild(el);
return container;
}
var width = $dropdown.width();
var opts = chosen.options || {},
showBtnsTresshold = opts.disable_select_all_none_buttons_tresshold || 0;
optionsCount = $field.children().length,
selectAllText = opts.select_all_text || ‘All’,
selectNoneText = opts.uncheck_all_text || ‘None’;
if( chosen.is_multiple && optionsCount >= showBtnsTresshold ) {
var selectAllEl = document.createElement(“a”),
selectAllElContainer = contained(selectAllEl),
selectNoneEl = document.createElement(“a”),
selectNoneElContainer = contained(selectNoneEl);
selectAllEl.appendChild( document.createTextNode( selectAllText ) );
selectNoneEl.appendChild( document.createTextNode( selectNoneText ) );
$dropdown.prepend(“

“);
$dropdown.prepend(selectNoneElContainer);
$dropdown.prepend(selectAllElContainer);
var $selectAllEl = $(selectAllEl),
$selectAllElContainer = $(selectAllElContainer),
$selectNoneEl = $(selectNoneEl),
$selectNoneElContainer = $(selectNoneElContainer);
var reservedSpacePerComp = (width – 25) / 2;
$selectNoneElContainer.addClass(“ui-chosen-selectNoneBtnContainer”)
.css(“float”, “right”).css(“padding”, “5px 8px 5px 0px”)
.css(“max-width”, reservedSpacePerComp+”px”)
.css(“max-height”, “15px”).css(“overflow”, “hidden”);
$selectAllElContainer.addClass(“ui-chosen-selectAllBtnContainer”)
.css(“float”, “left”).css(“padding”, “5px 5px 5px 7px”)
.css(“max-width”, reservedSpacePerComp+”px”)
.css(“max-height”, “15px”).css(“overflow”, “hidden”);
$selectAllEl.on(“click”, function(e) {
e.preventDefault();
$field.children().prop(‘selected’, true);
$field.trigger(‘chosen:updated’);
return false;
});
$selectNoneEl.on(“click”, function(e) {
e.preventDefault();
$field.children().prop(‘selected’, false);
$field.trigger(‘chosen:updated’);
return false;
});
}
}
});

.chosen-choices {
max-height: 150px;
}

https://stackoverflow.com/questions/11172269/select-all-and-remove-all-with-chosen-js

Generating Tables from SQL expressions

SELECT CONVERT(NVARCHAR(MAX), (SELECT
(SELECT ‘Top Thirty Sales per City (‘
+CONVERT(CHAR(11),GETDATE(),113)
+’)’ FOR XML PATH(”), TYPE) AS ‘caption’,
(SELECT ‘rank’ AS th, ‘Sales’ AS th, ‘City’ AS th FOR XML RAW(‘tr’), ELEMENTS, TYPE) AS ‘thead’,
(
SELECT TOP 30
CASE WHEN COUNT(*)>400 THEN ‘TextRed’
WHEN COUNT(*)>250 THEN ‘TextMauve’
ELSE ‘TextBlue’END AS [td/@class],
RANK() OVER (ORDER BY COUNT(*) desc) AS td ,
” AS [*],–hack to allow the use of PATH
COUNT(*) AS td,–PATH otherwise concatenates TDs
” AS [*],–hack to allow the use of PATH
a.City + ‘, ‘ + sp.Name + ‘, ‘ + cr.Name AS td
FROM Sales.SalesOrderHeader AS soh
JOIN Person.Address AS a
ON a.AddressID = soh.BillToAddressID
JOIN Person.StateProvince AS sp
ON sp.StateProvinceID = a.StateProvinceID
JOIN Person.CountryRegion AS cr
ON cr.CountryRegionCode = sp.CountryRegionCode
GROUP BY a.City + ‘, ‘ + sp.Name + ‘, ‘ + cr.Name
ORDER BY COUNT(*) DESC
FOR XML PATH(‘tr’), ELEMENTS, TYPE
) AS ‘tbody’
FOR XML PATH(”), ROOT(‘table’)));

https://www.red-gate.com/simple-talk/blogs/generating-html-sql-server-queries/

Webcam library for capturing JPEG/PNG images in JavaScript

// WebcamJS v1.0.23
// Webcam library for capturing JPEG/PNG images in JavaScript
// Attempts getUserMedia, falls back to Flash
// Author: Joseph Huckaby: http://github.com/jhuckaby
// Based on JPEGCam: http://code.google.com/p/jpegcam/
// Copyright (c) 2012 – 2017 Joseph Huckaby
// Licensed under the MIT License

USEFULL DMV MS SQL 2016

SELECT TOP(10) dows.*
FROM sys.dm_os_wait_stats as dows
ORDER BY dows.wait_time_ms DESC

–CPU BOUND QUERY
SELECT TOP 5
st.text,
qp.query_plan,
qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_worker_time DESC
GO

— Worst performing I/O bound queries
SELECT TOP 5
st.text,
qp.query_plan,
qs.*
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_logical_reads DESC
GO

–Find Slow Running Queries
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 20
CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2))
AS [Total Elapsed Duration (s)]
, qs.execution_count
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE
qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY total_elapsed_time DESC

–Find Long Running Queries
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT TOP 10
qs.total_elapsed_time AS [Total Time]
, qs.execution_count AS [Execution count]
, SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,
((CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE
qs.statement_end_offset
END – qs.statement_start_offset)/2) + 1) AS [Individual Query]
, qt.text AS [Parent Query]
, DB_NAME(qt.dbid) AS DatabaseName
, qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY [Total Time] DESC;

Stored procedure working when run manually, not running in SQL Agent Job XML sp_OACreate

1
down vote
accepted
I had this same issue when google geocoding and parsing the XML result. Seems that when run via an SQL job the XML result was being truncated.

Found the solution here: http://www.progtown.com/topic376715-a-problem-with-spoagetproperty-p2.html

Had to put:

SET TEXTSIZE 2147483647;
at the start of my stored proc.

The decision: in the procedure beginning to write the instruction

SET TEXTSIZE 2147483647

Or any other size, sufficient what to find room returned http the server for the answer.
The reason of such behavior of the server, I think, it is clear.
The moderator: and for zoom-in – the warning

http://www.progtown.com/topic376715-a-problem-with-spoagetproperty-p2.html

Detect hashchange and hash existed code run duplicate?

https://stackoverflow.com/questions/33056521/detect-hashchange-and-hash-existed-code-run-duplicate

var hash = ”;
$(document).load(function() {
$(window).trigger(‘hashchange’);
});

$(window).on(‘hashchange’, function() {
if (hash == window.location.hash) {
// do nothing, hash is the same as it was previously
return;
}

hash = window.location.hash;

if (hash) {
alert(“Apple”);
}
});

Prevent Truncation of Dynamically Generated Results in SQL Server Management Studio

Changing the Setting From The Tools Menu
In the Options dialog box of Tools Menu, expand Query Results, expand SQL Server and then select Results to Text as shown in the image below. In the right side panel change the value of Maximum number of characters displayed in each column to 8192. Click OK to save the changes as shown in the image below. The changes will go into effect once you open a new query window.

https://www.mssqltips.com/sqlservertip/2795/prevent-truncation-of-dynamically-generated-results-in-sql-server-management-studio/

Steps

Turn svg to font icons

About
IcoMoon is striving to build and provide the best iconography and icon management tool for perfectionists. IcoMoon’s icon library features only the very best icon sets out there. All of our icons are designed on a precise pixel grid. The IcoMoon app lets you build and use your own icon packs in many different formats including SVG, Polymer, PDF, XAML, CSH, icon font or simple PNG/CSS sprites.

https://icomoon.io/