Skip to content

Instantly share code, notes, and snippets.

@leppie
Created March 15, 2024 10:18
Show Gist options
  • Save leppie/09699b2f2a8eb808d6a4d2d0faae973a to your computer and use it in GitHub Desktop.
Save leppie/09699b2f2a8eb808d6a4d2d0faae973a to your computer and use it in GitHub Desktop.
Getting all transistive project references with DependenSee and SQL
drop table if exists Reference
drop table if exists Source
declare @json NVARCHAR(MAX) = (select Data from Deps); -- output from `DependenSee . -P -T ConsoleJson`
select *
into Source
from
(
SELECT Id, Name, Source = 'Project' from OPENJSON(JSON_QUERY(@json, '$.Projects')) WITH (Id nvarchar(255), Name nvarchar(255))
union
SELECT Id, Name, Source = 'Package' from OPENJSON(JSON_QUERY(@json, '$.Packages')) WITH (Id nvarchar(255), Name nvarchar(255))
) d
select distinct * into Reference from OPENJSON(JSON_QUERY(@json, '$.References')) WITH ([From] nvarchar(255), [To] nvarchar(255))
go
ALTER TABLE [dbo].[Source] ALTER COLUMN [Id] nvarchar(255) NOT NULL
GO
ALTER TABLE [dbo].[Source] ADD CONSTRAINT [PK_Source] PRIMARY KEY CLUSTERED ([Id] ASC)
GO
with refs as
(
select s.*, t.Id as RefId, t.Name as RefName, t.Source as RefType
from Source s
join Reference r on r.[From] = s.Id
join Source t on t.Id = r.[To]
),
rr as
(
select Origin = Id, *, Level = 0 from refs
union all
select rr.Origin, refs.*, Level = rr.Level + 1
from refs
join rr on rr.RefId = refs.Id
)
select distinct * from rr
order by Origin, Level, Id, RefType
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment