Data Lineage Scripts for Microsoft SQL Server and Azure SQL

  • This was removed by the editor as SPAM

  • Comments posted to this topic are about the item Data Lineage Scripts for Microsoft SQL Server and Azure SQL

    Tomaž Kaštrun | twitter: @tomaz_tsql | Github: | blog:

  • Never seen this function before!


    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Hi Phil,

    Wordpress has issues converting and showing T-SQL code correctly.

    For example the function fn_removelistChars, the function in WordPress code block gets strangely converted.

    This part:

    WHILE PATINDEX(@list,@txt) > 0

    SET @txt = REPLACE(@txt,SUBSTRING(@txt,PATINDEX(@list,@txt),1),'')

    RETURN @txt

    Gets automatically converted to:

    WHILE PATINDEX(@list,@txt) > 0

    SET @txt = REPLACE(cast(cast(cast(cast(cast(cast(@txt as nvarchar(max)) as nvarchar(max)) as nvarchar(max)) as nvarchar(max)) as nvarchar(max)) as nvarchar(max)),cast(cast(cast(cast(cast(cast(SUBSTRING(@txt as nvarchar(max)) as nvarchar(max)) as nvarchar(max)) as nvarchar(max)) as nvarchar(max)) as nvarchar(max)),cast(cast(cast(cast(cast(cast(PATINDEX(@list,@txt as nvarchar(max as nvarchar(max as nvarchar(max as nvarchar(max as nvarchar(max as nvarchar(max))))))))))))),1),'')

    RETURN @txt


    So please use the attached article files or check the github repository:

    Thanks, Tomaž

    Tomaž Kaštrun | twitter: @tomaz_tsql | Github: | blog:

  • That's a part of why I cringed so hard that it hurt when they announced they were shifting to a WordPress platform on the last change for the WebSite a few years ago.  It also made old code in old articles on the site get real ugly.  I especially hate what looks like inherent double-spacing on top of it not being T-SQL friendly even with the modifier that they bolted on, which also doesn't work in articles.

    We won't get into all the fact that it destroyed all my careful formatting in my last couple of articles by going "Oh look!  This is code and we're automatically going to put it into a WordPress code block"! 🙁

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Tomaz,

    >>Furthermore, you can run the script on Azure SQL Server, Azure SQL Database, Azure MI and Azure Synapse.<<

    I think this is a great effort, however you may want to fully test it out in Azure Synapse first.  Here is what I have found so far:

    • "CREATE OR ALTER PROCEDURE " is currently not supported.
    • "DROP TABLE IF EXISTS" is currently not supported
    • Variables of type table are not supported
    • Statements like the following are not supported:
      Statements like the following:
      declare @orig_q VARCHAR(MAX)
      SELECT @orig_q = COALESCE(@orig_q + ', ', '') + sp_text_fin
      FROM dbo.Query_results_no_comment
      order by rn asc​

      They trigger the following error:

      Msg 104473, Level 16, State 1, Line 2

      A variable that has been assigned in a SELECT statement cannot be included in a expression or assignment when used in conjunction with a from clause.

    All of these we can work around, for example the code previously posted we can probably change to something like this:

    declare @TableList varchar(max);
    select @TableList = string_agg(cast([name] as varchar(max)), ', ') WITHIN GROUP (ORDER BY [name])
    from sys.tables

    But the bigger question is does your script support all the Azure Synapse  statements such as CREATE TABLE AS ?

    Thanks again for sharing,


Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply