I don’t hate generating SQL dynamically like some may (apparently this is a relatively polarizing topic?) but I don’t love having to do it via SQL itself. Thankfully if I am needing to generate queries dynamically it’s some sort of one-off and not something that I need long term so my go-to for generating SQL is PowerShell. Please enjoy this short play in three acts about my recent need to generate a stupid amount of dynamic SQL.

The Hilarious Tragedy and Painful Comedy of Caring Too Much How Exactly How Your Data is Stored

Act I

Daytime in a typical american business office. American business is happening all about. J (a database administator) is busily businessing it up. Chet (an SRE) approaches distressed.

CHET: J, I have _terrible news! I’ve been troubleshooting one of our business applications and it sure has PROBLEMS! Bad problems with the database: Can you you join this call with our business vendor partner to discuss it?

J: Sure–

Chet immediately punches a button on J’s phone. The VENDOR is suddenly revealed in a spotlight. They are an enormous spider (multiple limbs the whole bit). Despite their overabundance of limbs they are still cradling the phone between their shoulder and disturbing spider head. They are working at a computer immediately in front of them but their other spidery limbs are also constantly connecting webbing to a large data diagram off to their side.

VENDOR: So the problem is the application is expecting the database to be 64 bit but the database is 32 bit. All the integers are wrong!

J: What do you mean the database is 32 bit? We’ve been running on a 64 bit implementation of SQL Server for the last 10 years.

VENDOR: The integers man! They are not big enough!

J: I don’t understand: are we running out of available values for the integers? Also not a man…

VENDOR: No they just aren’t big enough. All the integers: they must be bigger! Larger! More enormous! Here let me demonstrate in your test environment…

Lights reveal another database connected to J’s computer the Vendor’s spidery limbs now begin to connect webbing to this new structure.

VENDOR: See we just need to take all of these INTs and make them bigger: BIGINTs.

Their spider limbs removed objects from the database and replac them with larger cubes.

BIGGERER! BETTERER! laughs

J: There are over 6000 tables with 35 thousand columns that need to be converted. Do you have a tool for that?

VENDOR: spidery grin, whispers No.

lights dim on the Vendor as their desk is rotated to the back of the stage

Act II

AS the stage rotates J and Chet stand and walk amongst a series of boxes with the names of the tables in the database (doc187, keyitems3199 etc.) each of the box is subdivided into cubes that show parts of the table and their data type. The boxes are connected by strings from subcubes to subcube. Many of the tables contain multiple sub-cubes called <name>_ID. J removes one of these cubes.

J: So the actual problem (for some reason) is the application is checking to see how the value is stored. If the application is “64 bit”

makes air quotes

and the datatype is not BIGINT it will throw an error. So all of these

gestures to cube

need to be taken out and replaced with the exact same thing except bigger.

produces a cube that is twice the size of the original and attaches it back to the structure

CHET: That doesn’t seem so bad. That’s what the business vendor did in our test system.

J: The problem is that in our production system many of these are used by other objects: indexes, defaults, keys and the like.

J removes another cube and we see that it is connected back to the larger structure it was part of

J: We can’t just change the type because these connections depend on what is already there. Additionally our test system was only 10 GB of data and production is 50 times that! I’ve started trying to adapt the code they used they but it just doesn’t scale and they really only accounted for one kind of dependency. I’ve run some initial tests and currently the code to update the largest table simply times out and never completes.

J produces a tiny pair of plyers that is clearly not up for the task of dismantling and re-assembling the database and gestures futilely. The Vendor drops down from above and waves their spidery limbs at the database J and Chet stand in.

VENDOR: We’ll need to approve any change script for updating the data structures, but the application will also potentially add tables and columns that also need updating between your development and when it is finally run.

CHET: Even worse we can’t do business while this application is unavailable so our update window is limited to a single weekend.

J: If we can update the database incrementally perhaps we can do the updating over a few weekends and then switch to “64 bit” air quotes

VENDOR: soft laughter Our 32 bit business application will do no business with BIG INTEGERS! They would be TOO BIG! Inconceivable!

J pulls a bottle of whiskey out of one of the cubes and pours a glass as the lights begin to fade.

VENDOR: We still have no formal update process…

Blackout.

Act III

Lights up on on J on the database. J is wearing technical looking armor. The database has grown and there are now more cubes (tables) each with their own subcubes (columns). On the other side spidery legs continue to add cubes and strings to the structure.

J: So here we are. We need to take all of these and convert them to continue doing the businesses. We could do it in SQL but honestly getting object definitions ensuring they are scripted out correctly–it’s a whole thing that sounds awful. And honestly it’s a solved problem, that’s why I equipped my self with dbatools.

A shoulder mounted cannon rotates into position it is labeled “PowerShell.” J turns a selector on his guantlet to “DBATools.”

Laser lights begin streaking from the shoulder cannon into the mass of cubes and strings. It begins to pulsate and spin.

J: Using DBATools I can get a list of tables that have columns with integer columns. We can then script out each table (including all the dependent objects) with a new name and the correct data type. Once we create the replacement table we can bulk load it by transferring the data from the original table. Once the data is moved we can drop the original table and rename the new one to replace it.

The database stops spinning, it has doubled in size physically but is now shining gold. Chet appears from inside of it.

CHET: Hooray! With the database updated we can use the most current version of the business application for business!

J: And I’ve saved a sample of this script out on github. Of course this was just a copy of the production database and we need to perform testing (Never just run scripts untested in production if you can help it). And we even that seems to pass muster we still need the vendor to sign off the changes.

The shoulder cannon launches itself towards the Vendor (still only seen as spidery limbs from above). The missile is caught by the two of the limbs which begin to wrap it in webbing.

CHET: I am sure they will get back to us as soon as possible!

The missile slowly ascends into the fly system. Dry laughter from the Vendor is heard.