Cláudio Silva's Blog

SQL, PowerShell, and more from a SQL Server DBA

Menu

Skip to content
  • Home
  • SQLServer
  • PowerShell
    • PowerShell Modules Central
  • About Cláudio
  • Contact

Daily Archives: August 24, 2018

Standard

Posted by

Cláudio Silva

Posted on

August 24, 2018

Posted under

PowerShell, SQLServer

Comments

2 Comments

Leveraging on SQL Server metadata

I’m working on a project where I need to convert Firebird SQL code into T-SQL code.
No schema, just the modules. There are more than 1000 objects between stored procedures, views, triggers, user-defined data types, etc.

First – the pain…

While checking the Firebird reference manuals I saw a lot of different concepts (Selectable Stored Procedures – Yes you can do SELECT FROM StoredProcedure) and different functions names with different syntax compared to T-SQL.

With this in mind, can you imagine doing a code migration between two different SQL flavours that contains more than 60000 lines of code by hand? 😨

and…the false alarm – My approach

When I started to think about the possible approaches to this huge amount of work I will have, one of the ideas (and actually the one I ended up doing) was write a find/replace function in PowerShell that can receive an hashtable with “pattern to search” and the “pattern to replace”.

Because the -replace method allows the use of regular expressions this become very powerful.

Don’t reinvent the wheel

I did a search and found a gist from Matthew Steeples that has a function to do a find/replace of a single string on a file using regular expressions.

I picked this script and I have adapted to my reality. This means, I have changed the code in order to:

  • Accept an ordered hashtable and this way do multiple changes.
  • Open the file and do all the changes before save it again.

You can find on my GitHub repository the Set-Expression PowerShell function I ended with.

Filling the hastable

To fill the hashtable I had to make a match between the two SQL flavours.

Just a couple of examples to ilustrate the differences:

Firebird has the similar function which translate to LIKE '%%' on T-SQL
Other example is the SUBSTRING function which has the following structure SUBSTRING([field] from [start_position] for [number of chars]) which, in order to translate for T-SQL, we just need to replace the “from” and “for” words by a comma (“,”).

Everything was going well, work was progressing at a good pace until…

The pain strikes back

These databases make a heavy use of triggers. The main use of it is to generate a new ID to a column based on a Firebird DOMAIN object (SEQUENCES in T-SQL) on BEFORE INSERT (INSTEAD OF in T-SQL) triggers.

Note: “But they can use IDENTITY columns…” – Exactly what I have proposed, and for some tables they have implemented but for the majority it wasn’t possible.

Firebird make this somehow easy. This is a code example from an Firebird trigger:

CREATE OR ALTER TRIGGER Customers_BI FOR Customers
ACTIVE BEFORE INSERT POSITION 0
as
begin
  if (new.id is null) then
    new.id = gen_id(gen_Customers_id,1);
end;

in T-SQL syntax, we need to set the whole INSERT statement with all columns from the INSERTED table. The equivalent code can be something like this:

CREATE TRIGGER dbo.Customers_BI
ON dbo.Customers
INSTEAD OF INSERT
AS
    BEGIN
          IF EXISTS
            (
                SELECT
                       1
                  FROM INSERTED
                 WHERE Inserted.ID IS NULL
            )
              BEGIN
                    INSERT INTO dbo.Customers (ID, <other columns>
                    SELECT 
                           NEXT VALUE FOR dbo.gen_Customers_id
                         , <other columns> 
                    FROM INSERTED;
             END
    END;

“so you need to replace 1 line with an INSERT statement with all columns from the table…”

Yes that much work..on almost 300 triggers! Feeling the pain, right?

Here is where the SQL Server metadata joins the party!

This is just an example, but with it you can imagine the others 🙂

I wrote a piece of T-SQL (not pretty but it works) that creates, for each table, the hashtable entry for my find/replace function.

SELECT
      A.TABLE_NAME
    , '''(FOR )(\b' + A.TABLE_NAME
      + '\b)((.|\r\n){1,})(\bACTIVE BEFORE INSERT POSITION 0\b)(\r\n)(AS)(\r\n)(BEGIN)(\r\n)((.|\n){1,})(gen_id\()(\w{1,})((.|\r\n)*?)(?=end;)(.*)'' = "ON `$2`$3INSTEAD OF INSERT`$3`$7`$8`$9`$10`$11`$12 `$3INSERT INTO `$2 ( '
      + A.ColumnList + ' ) `$3SELECT NEXT VALUE FOR dbo.`$14, ' + A.ColumnList + ' FROM INSERTED `$3 --`$13`$14`$15`$16`$17"'
    , '''(FOR )(\b' + A.TABLE_NAME
      + '\b)((.|\r\n){1,})(\bACTIVE BEFORE UPDATE POSITION 0\b)(\r\n)(AS)(\r\n)(BEGIN)(\r\n)((.|\r\n){1,})((.|\r\n)*?)(?=end;)(.*)'' = "ON `$2`$3INSTEAD OF UPDATE`$3`$7`$8`$9 `$3UPDATE `$2 `$3SET '
      + A.ColumnListUpdate + ', `$10`$11 FROM INSERTED `$3 `$15"'
  FROM
      (
          SELECT DISTINCT
                 ISC.TABLE_NAME
               , STUFF((
                           SELECT
                                  ', ' + ISC1.COLUMN_NAME
                             FROM INFORMATION_SCHEMA.COLUMNS AS ISC1
                            WHERE OBJECT_ID(ISC1.TABLE_NAME) = OBJECT_ID(ISC.TABLE_NAME)
                            ORDER BY ISC1.ORDINAL_POSITION
                           FOR XML PATH('')
                       )
                     , 1
                     , 1
                     , ''
                      ) AS ColumnList
               , STUFF((
                           SELECT
                                  ', ' + ISC1.COLUMN_NAME + ' = ' + ISC1.COLUMN_NAME
                             FROM INFORMATION_SCHEMA.COLUMNS AS ISC1
                            WHERE OBJECT_ID(ISC1.TABLE_NAME) = OBJECT_ID(ISC.TABLE_NAME)
                            ORDER BY ISC1.ORDINAL_POSITION
                           FOR XML PATH('')
                       )
                     , 1
                     , 1
                     , ''
                      ) AS ColumnListUpdate
            FROM INFORMATION_SCHEMA.COLUMNS AS ISC
                 INNER JOIN sys.sysobjects AS so
                    ON ISC.TABLE_NAME = so.name
                   AND so.type = 'U'
           WHERE ISC.TABLE_NAME NOT IN ('sysdiagrams', 'Numbers')
      ) AS A
 ORDER BY A.TABLE_NAME;

Let’s say I have a Customers table with 3 columns ID, NAME, COMPANY_NAME the output will be:

Example of the full string for the INSTEAD OF INSERT trigger:

'(FOR )(\bCustomers\b)((.|\r\n){1,})(\bACTIVE BEFORE INSERT POSITION 0\b)(\r\n)(AS)(\r\n)(BEGIN)(\r\n)((.|\n){1,})(gen_id\()(\w{1,})((.|\r\n)*?)(?=end;)(.*)' = "ON `$2`$3INSTEAD OF INSERT`$3`$7`$8`$9`$10`$11`$12 `$3INSERT INTO `$2 (  ID, NAME, COMPANY_NAME ) `$3SELECT NEXT VALUE FOR dbo.`$14,  ID, NAME, COMPANY_NAME FROM INSERTED `$3 --`$13`$14`$15`$16`$17"`

This way I can just copy/paste the result to my hashtable and run the PowerShell function on my triggers’ files.

All’s well that ends well

This made possible to automate, I would say, 95% of the work I have to do on each trigger object.

The 5% left are purposeful and intended to format the code using Redgate SQL Prompt (formatted the code using the great CTRL + K, Y shortcut) once I open the script on SQL Server Management Studio and test the object compilation.

Other examples?

I applied the same recipe on scripts with user-defined data types (UDDT). I picked the system data type and created entries to the hashtable with the proper replace.

SELECT
       '''\b' + T2.name + '\b'' = "' + T1.name + CASE
                                                     WHEN T2.system_type_id IN (231, 239) THEN '(' + CASE
                                                                                                         WHEN T2.max_length = -1 THEN 'MAX'
                                                                                                         ELSE CAST(T2.max_length / 2 AS VARCHAR(10))
                                                                                                     END + ')'
                                                     WHEN T2.system_type_id IN (175, 167) THEN '(' + CASE
                                                                                                         WHEN T2.max_length = -1 THEN 'MAX'
                                                                                                         ELSE CAST(T2.max_length AS VARCHAR(10))
                                                                                                     END + ')'
                                                     ELSE ''
                                                 END + '"'
  FROM sys.types AS T1
       INNER JOIN sys.types AS T2
          ON T1.user_type_id = T2.system_type_id
 WHERE T2.user_type_id > 256;
Practical example:

UDDT named INT_VALUE, that represents an INT, used in the following way CAST(column AS INT_VALUE) needs to be replaced as
CAST(column as INT).

Why we need to do this replace? I have written about it on my blog post Using CAST() function with User-Defined Data Types…Did you know… take a look.

Wrap up – Life saver and time saved

Leverage on SQL Server metadata to generate quick code and use it inside SQL Server or outside like I did it here!

I did some math and I have estimated that I would need several months to finish this task doing everything by hand.

Everything could go wrong doing that way:

  • This is an heavely repetitive task
  • highly prone to errors
  • a witch hunt when problems arise

After all, with this approach I have made in less than one month! And, half of those days were to tweak the regular expressions/hashtable.

Automation? You gotta to love it! 🙂

Thanks for reading!

Follow me

  • Twitter
  • LinkedIn
  • GitHub

PowerShell Modules Central

PowerShell Modules Central
PowerShell Modules Central

dbatools

dbatools - Instance migrations & best practice implementations have never been safer, faster or freer.

dbatools - Instance migrations & best practice implementations have never been safer, faster or freer.

dbatools - Instance migrations & best practice implementations have never been safer, faster or freer.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 30 other followers

Recent Posts

  • More PowerShell Remoting coverage in dbatools August 20, 2019
  • Allow SQL Server Agent jobs management for non sysadmins August 13, 2019
  • dbatools v1.0? It’s available – Check it out! June 19, 2019
  • “Ups…I have deleted some data. Can you put it back?” – dbatools for the rescue May 17, 2019
  • Scripting SQL Server objects with dbatools – Beyond default options May 15, 2019

Archives

Follow me on Twitter

My Tweets
Follow Cláudio Silva's Blog on WordPress.com
Powered by WordPress.com.
Cancel