tonesandtones

--Transact-SQL script to identify and report tables/columns linked by circular FK references
--Original source https://azure.microsoft.com/en-us/blog/finding-circular-foreign-key-references/
--Reformatted by tonesandtones and verified working against a new Azure SQL DB instance on 2018-07-16
--
--
--Produces output like this:
-- dbo.Triggers -> dbo.Conversations -> dbo.Triggers
-- dbo.Conversations -> dbo.Triggers -> dbo.Conversations
-- dbo.AbpOrganizationUnits -> dbo.AbpOrganizationUnits
-- dbo.AbpUsers -> dbo.AbpUsers

tonesandtones

--2018-07-18 Updated to use new RANKX function to deal with duplicate timestamp values.
--
--When there are duplicate timestamp values in the dataset, using the previous method (with EARLIER()) would create
--index values that run, eg, 122, 123, 123, 125, 126.
--Supposing therer were 100 new items per row, and ~5000 total (so total would run 4900, 5000, 5100, 5200, etc),
--the ValueChange would run 100, 100, 5000(!!), 100, 100.
--Now, using a RANKX() with Dense enumeration (https://msdn.microsoft.com/en-us/query-bi/dax/rankx-function-dax),
--we can get dense indices that don't skip, so we can lookup Index-1 and know it always exists.

------------------------------------------

tonesandtones

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Globalization;
using System.Net.Http;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using Flurl.Http;