Data Lineage for Microsoft SQL Server, Azure SQL Server and Azure Synapse
MIT License
Data Lineage Transact SQL (T-SQL) for Microsoft SQL Server or Azure SQL Server enables you to find the data origins and data destinations in your query. It gives you the visibility over query data columns and ability to track the changes over time.
Clean your code of in-line and multiple lines of --comments or slash star comments from better visibility and greater readability.
Run Remove_comments.sql to create a procedure. Strip and remove all comments from your T-SQL query by using dbo.remove_comments procedure
-- Run procedure dbo.remove_comments
EXEC dbo.remove_comments
@procedure_name = N'dbo.MySample_procedure'
Run TSQL_data_lineage.sql file to create a lineage procedure. This script includes the removal of comments and special characters and creates the data lineage.
-- Get your query:
DECLARE @test_query VARCHAR(MAX) = '
-- This is a sample query to test data lineage
SELECT
s.[BusinessEntityID]
,p.[Title]
,p.[FirstName]
,p.[MiddleName]
-- ,p.[LastName]
,p.[Suffix]
,e.[JobTitle] as JobName
,p.[EmailPromotion]
,s.[SalesQuota]
,s.[SalesYTD]
,s.[SalesLastYear]
,( SELECT GETDATE() ) AS DateNow
,( select count(*) FROM [AdventureWorks2014].sales.[SalesPerson] ) as totalSales
/*
Adding some comments!
*/
FROM [AdventureWorks2014].sales.[SalesPerson] s
LEFT JOIN [AdventureWorks2014].[HumanResources].[Employee] e
ON e.[BusinessEntityID] = s.[BusinessEntityID]
INNER JOIN [AdventureWorks2014].[Person].[Person] AS p
ON p.[BusinessEntityID] = s.[BusinessEntityID]
'
-- And run the procedure with single input parameter
EXEC dbo.TSQL_data_lineage
@InputQuery = @test_query
The script works with any of the following versions:
The easiest way to get started is with fork or clone the repository.
You can follow the steps below to clone the repository.
git clone https://github.com/tomaztk/SQLServer-Data-Lineage.git
Remove comments from your T-SQL code (Blog post)
Ideas, code collaboration or any other contributions of any kind is highly appreciated! Fork the repository, add your code.