Hi,
Given a number of tables (from staging up to denormalised tables), views and stored procedures. How can I identify where a column in a view or table originates from or is used?
I googled on "data lineage" and it apears they are all part of a "platform", a big solution.
How can I parse SQL scripts or SP scripts to find out the source and the destination tables/views and columns?
I am not sure I understand but one way is to do
Sp_helptext your_view
Sp_helptext your_procedure
Or in ssms, open database where artifacts exist, open views node or procedures node, right click and select create
Sp_helptext returns the definition of the view (or SP). (I learned something new)
Let me explain what we want.
Suppose we have a database with a number of tables and views.
create table dbo.DaTable(
id int identity NOT NULL primary key
, a_code int NOT NULL
, amount int NOT NULL
, price decimal(8, 2) NOT NULL
, client_id int NOT NULL
)
GO
CREATE VIEW dbo.view2 AS
SELECT id as view_id, a_code as MyCode, amount MyAmount, price
FROM dbo.DaTable
WHERE a_code IN (6, 12)
GO
create table dbo.aggregatedDaTable(
id int identity NOT NULL primary key
, Filtered_code int NOT NULL
, ordered_amount int NOT NULL
, total_price decimal(8, 2) NOT NULL
)
GO
A SP could contain this code:
INSERT INTO dbo.aggregatedDaTable (Filtered_code, ordered_amount, total_price)
SELECT MyCode, SUM(MyAmount), SUM(MyAmount * price)
FROM dbo.view2
GROUP BY MyCode
We want to know in what tables and views data from dbo.DaTable is used.
We want to know where the data in dbo.aggregatedDaTable comes from.
If we can extract the dependencies between the tables/views and their columns, we can query that data set. We need the dependencies in a format somewhat like this:
Table_View Column SrcTable SourceColumn
dbo.aggregatedDaTable id null null
dbo.aggregatedDaTable Filtered_code dbo.view2 MyCode
dbo.aggregatedDaTable ordered_amount dbo.view2 MyAmount
dbo.aggregatedDaTable total_price dbo.view2 MyAmount
dbo.aggregatedDaTable total_price dbo.view2 price
dbo.view2 view_id dbo.DaTable id
dbo.view2 MyCode dbo.DaTable a_code
dbo.view2 MyAmount dbo.DaTable amount
dbo.view2 price dbo.DaTable price
If you want to know where the data in dbo.aggregatedDaTable.total_price comes from, you get dbo.view2.MyAmount and price.
If you want to know where the data in dbo.view2.MyAmount comes from, you get dbo.DaTable.Amount.
When someone finds an anomaly in dbo.aggregatedDaTable.total_price, you now have a means to find where the data originates from. It may be a bug in the view definition or a data quality problem in the staging table.
Suppose, you find out the data in the staging table dbo.DaTable is corrupted is some way, you can now find out all the tables and views that data is used in.
This should get you on the right track. What you ask for could be done using dynamic sql as you can see the sql artifacts are referenced using a single quote, so you doing this dynamically is possible. heading off to work. will try to look later on
SELECT referencing_schema_name
,referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.projects', 'Object')
select referenced_schema_name, referenced_entity_name,referenced_minor_name
from sys.dm_sql_referenced_entities('dbo.projects_sp', 'Object')
select referenced_schema_name, referenced_entity_name,referenced_minor_name
from sys.dm_sql_referenced_entities('dbo.projects', 'Object')
select *
from sys.dm_sql_referencing_entities('dbo.projects', 'Object') x
ok try this and post back
SELECT o.name AS ObjectName,
c.name as ColumnName,
t.name as SrcTable,
o.type_desc as ObjectType
FROM plt.sys.sql_expression_dependencies sed
inner JOIN sys.objects o ON sed.referencing_id = o.[object_id]
inner join sys.tables t on t.name = sed.referenced_entity_name
inner join sys.columns c on c.object_id = t.object_id
Download and install SQL Search from Redgate - then use that to search the system for any matching values.