Author: jeffld

SQL Sysobjects

This SQL is helpful when searching for specific strings within SQL code. I have used this code to search in databases too many times to mention. It is especially helpful when looking at a legacy database that I have never looked at before when trying to find things.

SELECT *
FROM sysobjects
WHERE id IN (
		SELECT id
		FROM syscomments
		WHERE TEXT LIKE '%Outstanding orders confirmed %' 
		)
ORDER BY NAME

A cryptarithmetic puzzle

A puzzle where a mathematical equation is written using letters instead of numbers. Each letter represents a digit from 0 to 9, and no two letters can represent the same digit.
TOO + TOO + TOO + TOO = GOOD

  $i=0;

   echo "<h1>A cryptarithmetic puzzle</h1>";
   echo "<p>A puzzle where a mathematical equation is written using letters instead of numbers. Each letter represents a digit from 0 to 9, and no two letters can represent the same digit.</p>";

   echo "<h4>TOO + TOO + TOO + TOO = GOOD</h4>";

 
   echo "<table border=1><tr><td>Solutions</td></tr>";
    for ($T = 0; $T < 10; $T++)
    {
        for ($O = 0; $O < 10; $O++)
        {
            for ($G = 0; $G < 10; $G++)
            {
                for ($D = 0; $D < 10; $D++)
                {
                    if (($D == $G) || ($D == $O) || ($D == $T) || ($G == $O) || ($G == $T) || ($O == $T))
                        continue;
                    else if (400 * $T + 40 * $O + 4 * $O == 1000 * $G + 100 * $O + 10 * $O + $D)
                    {
        
                        $arrT[$i]=$T;
                        $arrO[$i]=$O;
                        $arrG[$i]=$G;
                        $arrD[$i]=$D;
                        
                        echo "<tr><td>";
                        echo "T = ".$T."<br/>";
                        echo "O = ".$O."<br/>";
                        echo "G = ".$G."<br/>";
                        echo "D = ".$D."<br/>";
                        echo "</td></tr>";
                        
                        $i=$i+1;
                        
                        break;
                    }
                }
               
            }
        }
    }
   echo "</table>";
   
   echo "<h4>PROOF</h4>";
   
   for ($j = 0; $j < $i; $j++) {
   
   echo "<p>".$arrT[$j].$arrO[$j].$arrO[$j]." + ".$arrT[$j].$arrO[$j].$arrO[$j]." + ".$arrT[$j].$arrO[$j].$arrO[$j]." + ".$arrT[$j].$arrO[$j].$arrO[$j]." = ".$arrG[$j].$arrO[$j].$arrO[$j].$arrD[$j]."<p>";
   
   }

Demo Cryptarithmetic

CSS Graph Paper Background

Nice and dark graph for printing graph paper. May need to adjust the 4th parameter of the rgba function to make the lines lighter.

body {
    background:
        linear-gradient(-90deg, rgba(0, 0, 0, 1.03) 1px, transparent 1px),
        linear-gradient(rgba(0, 0, 0, 1.03) 1px, transparent 1px),
        linear-gradient(-90deg, rgba(0, 0, 0, 1.03) 1px, transparent 1px),
        linear-gradient(rgba(0, 0, 0, 1.03) 1px, transparent 1px),
        linear-gradient(transparent 3px, transparent 3px, transparent 78px, transparent 78px),
        linear-gradient(-90deg, transparent 1px, transparent 1px),
        linear-gradient(-90deg, transparent 3px, transparent 3px, transparent 78px, transparent 78px),
        linear-gradient(transparent 1px, transparent 1px), transparent;
    background-size:
        10px 10px,
        10px 10px,
        10px 10px,
        10px 10px,
        10px 10px,
        10px 10px,
        10px 10px,
        10px 10px;
}

Visualize SQL XML Execution Plan using HTML

I’ve always used the Microsoft SQL Management Studio Execution plan feature to visualize the query plan. However today I was experimenting with the SQL XML showplan statement to see if I could learn anything more about execution plans by looking more closely at the code. As soon as I saw the XML it occurred to me that I could use XSLT to convert the output to HTML similar to the way that the SQL Management studio did it. I was curious to try, but before I writing my own, I checked to see if anyone else did it already and found this on github:

So, to demonstrate I created a simple SQL using temp tables so that I could perform some very basic tests. 

IF OBJECT_ID('tempdb..#header') IS NOT NULL DROP TABLE #header
IF OBJECT_ID('tempdb..#detail') IS NOT NULL DROP TABLE #detail

set nocount on

create table #header
(
 order_no int,
 name  varchar(30),
 email varchar(255),
)

 create table #detail
(
 order_no int,
 item  varchar(30),
 item_code  varchar(30)
 )

Insert some sample data 

 insert into #header (order_no,name,email) values (121,'Joe Blogs','j.bloggs@bar.com')
 insert into #header (order_no,name,email) values (122,'Bill Smith','B.Smith@foo.com')

 insert into #detail (order_no,item,item_code) values (121,'Item 1','IT-001')
 insert into #detail (order_no,item,item_code) values (121,'Item 1','IT-002')
 insert into #detail (order_no,item,item_code) values (121,'Item 1','EV-003')
 
 insert into #detail (order_no,item,item_code) values (122,'Item 1','IT-001')
 insert into #detail (order_no,item,item_code) values (122,'Item 2','IT-002')
 insert into #detail (order_no,item,item_code) values (122,'Item 3','IT-003')

Turn on the SHOWPLAN_XML in SQL and run query to produce the XML.

SET SHOWPLAN_XML ON;
go
 SELECT
      h.order_no
    , h.name
    , h.email
    , d.item
    , d.item_code
FROM #header h
JOIN #detail AS d ON h.order_no = d.order_no
WHERE NOT EXISTS (
      SELECT NULL
      FROM #detail od
      WHERE od.item_code = 'EV-003'
      AND h.order_no = od.order_no
      )
GO
SET SHOWPLAN_XML Off;
GO

Then copy and paste the output XML data into a file and upload to the demo page that uses Justins HTML query plan visualizer library.

HTML XML Upload demo