Data Lineage Scripts for Microsoft SQL Server and Azure SQL

Question

Post reply

Data Lineage Scripts for Microsoft SQL Server and Azure SQL

October 18, 2022 at 7:19 am

This was removed by the editor as SPAM
tomaz.kastrun

SSCrazy

Points: 2141
More actions
October 19, 2022 at 12:00 am

#4098844

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: https://github.com/tomaztk | blog: https://tomaztsql.wordpress.com/

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Never seen this function before!

DataLength

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

tomaz.kastrun SSCrazy Points: 2141 More actions · Answer 2

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: https://github.com/tomaztk/SQLServer-Data-Lineage

Thanks, Tomaž

Tomaž Kaštrun | twitter: @tomaz_tsql | Github: https://github.com/tomaztk | blog: https://tomaztsql.wordpress.com/

Jeff Moden SSC Guru Points: 1004321 More actions · Answer 3

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)

Anthony Perkins SSC Enthusiast Points: 199 More actions · Answer 4

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,

Anthony