As I was working on another problem the other day, I had the need to compare two tables that had been created and modified by AI. I created fairly insane column names to test to see what the AI would do with a table that had columns with spaces, brackets, smart quotes, etc. in them.

Then I needed to display the output. I didn’t have a comparison tool handy, and even if I did, getting the output from such a tool isn’t always super simple. So having recently written about comparing sets of data, I decided to just write a quick query, which I will show later in the blog.

The test case

I created a table like this (with a lot more columns, but for my purposes here, these three columns are good enough).

CREATE TABLE dbo.[Table Name]
(
Id nvarchar(2000) NOT NULL PRIMARY KEY,
[Smart ’ Quote] varchar(30) NULL,
['[Bracketed]]' and Quoted Name] varchar(20)
)

The first thing to know is that this IS a valid table declaration. The Id column being nvarchar(2000) is a problem, as it will give you this error:

Warning! The maximum key length for a clustered index is 900 bytes. The index 'PK__Table Na__3214EC0700FF802C' has maximum length of 4000 bytes. For some combination of large values, the insert/update operation will fail.

But it absolutely will execute and you can use it like any other table, well, any other table that you need to copy and past part of the name that is.

Part of my testing was simply to see if the AI would change the name or datatype. I asked several AI’t to give me a query that “worked” and they all completely ignored the fact that this will in fact execute and possibly do what someone actually wanted.

I will not vouch for anyone that would put this into production, but that doesn’t mean I would alter it to my tastes just because it was not a great query. I may schedule many meetings to discuss coding standards, but that is me.

One such output for this was as follows. I say one such, because every tool I tried changed the names in various ways. They both left the datatypes as is and didn’t warn me of that impending warning message (though to be fair, I didn’t tell them what RDBMS I was targeting, even if they all seemed to know.

I rename the table name in the next two declarations just to be able to compare easily, but the were originally named Table Name as was the original:

CREATE TABLE dbo.[Table Name One]
(
  Id nvarchar(2000) NOT NULL PRIMARY KEY,
  [Smart ' Quote] varchar(30) NULL,
  [Bracketed and Quoted Name] varchar(20)
);

CREATE TABLE dbo.[Table Name Two]
(
  Id nvarchar(2000) NOT NULL PRIMARY KEY,
  [Smart ’ Quote] varchar(30) NULL,
  ['[Bracketed]]'' and Quoted Name] varchar(20) NULL
);

Let’s compare!

For simplicity sake, I created a query that I could can check for any column name changes. It simply does a FULL OUTER JOIN on rows from the INFORMATION_SCHEMA.COLUMNS views and separates differences.

--note, do not include brackets that aren't part of the name in the objects
DECLARE @SchemaName1 sysname = 'dbo', @TableName1 sysname = 'Table Name';
DECLARE @SchemaName2 sysname = 'dbo', @TableName2 sysname = 'Table Name Two';

SELECT   T1.COLUMN_NAME AS FromTable, T2.COLUMN_NAME AS ToTable
FROM
(
  SELECT *
  FROM   INFORMATION_SCHEMA.COLUMNS t1
  WHERE  t1.TABLE_SCHEMA = @SchemaName1
    AND t1.TABLE_NAME = @TableName1
) AS T1
FULL OUTER JOIN
(
  SELECT *
  FROM   INFORMATION_SCHEMA.COLUMNS t2
  WHERE  t2.TABLE_SCHEMA = @SchemaName2
    AND t2.TABLE_NAME = @TableName2
) AS T2
ON T1.COLUMN_NAME = T2.COLUMN_NAME
--you will want to tailor this to your needs. Sorting
--alphabetically, I sorted by the order in the table
--since my table column order hasn't changed, then by
--the column name so it would put the nulls in the
--right order.
ORDER BY COALESCE(   T1.ORDINAL_POSITION,
T2.ORDINAL_POSITION
), T1.COLUMN_NAME DESC;

Now you can see in the output the differences between the column names. Turns out, none of the columns except for the Id column match.

FromTable                       ToTable
------------------------------- --------------------------
Id                              Id
Smart ’ Quote                   NULL
NULL                            Smart ' Quote
'[Bracketed]' and Quoted Name   NULL
NULL                            Bracketed and Quoted Name

This tended to occur quite often when I tried bracketed names that looked more like trivia questions than reality. Though I did expect the AI tools to understand the rules a lot more clearly and not get these basics correct. They are certainly tricky basics, as I didn’t get the name with the embedded brackets right the first or second time.

Change the variables to:

DECLARE @SchemaName1 sysname = 'dbo', @TableName1 sysname = 'Table Name';
DECLARE @SchemaName2 sysname = 'dbo', @TableName2 sysname = 'Table Name Two';

Now the output becomes:

FromTable                       ToTable
------------------------------- --------------------------
Id                              Id
Smart ’ Quote                   Smart ’ Quote
'[Bracketed]' and Quoted Name   NULL
NULL                            '[Bracketed]'' and Quoted Name

Which you can see is closer now, because it got the Smart Quote column correct, but it did feel the need to add another quote to the second quote in the “Bracketed” name. This was the first time that an AI tool actually left the smart quote alone in a name. Still, missed it by that much.

The Point?

AI is getting better and better with tasks like this. I have run this test a few times with several AI tools and for cleaning up things like smart quotes in a where clause:

WHERE  ColumnName = ’value’ --instead of 'value'

It does pretty well. But with this really way out there edge case, the tools do not handle it well. So, we need to do some comparisons, either with queries like this, or using compare tools, or even just scripting things out and comparing them.

I tend to have lots of these little scripts hanging around in order to compare something to throw into a document and putting them on the blog makes them easier for me to get, and for anyone else to use.