CREATE TABLE [dbo].[UserResponsibilityProfileDescription](
	[UserResponsibilityProfileDescriptionId] [uniqueidentifier] NOT NULL PRIMARY KEY,
	[DeletedTime] [datetime2](7) NULL,
	[Name] [nvarchar](50) NOT NULL)

CREATE TABLE [dbo].[UserResponsibilityProfile](
	[UserResponsibilityProfileId] [uniqueidentifier] NOT NULL PRIMARY KEY,
	[DeletedTime] [datetime2](7) NULL,
	[UserResponsibilityProfileDescriptionId] [uniqueidentifier] NOT NULL,
        CONSTRAINT [FK_UserResponsibilityProfileActivity_UserResponsibilityProfile] FOREIGN KEY([UserResponsibilityProfileId])
        REFERENCES [dbo].[UserResponsibilityProfile] ([UserResponsibilityProfileId])

with r as
(
 select
  t.object_id, quotename(schema_name(t.schema_id)) + N'.' + quotename(t.name) as table_name, 1 as [level],
  '(' + cast(t.object_id as varchar(max)) + ')' as [path]
 from
  sys.tables t
 where
  not exists(select 1 from sys.foreign_keys where referenced_object_id = t.object_id)

 union all

 select
  fk.referenced_object_id, quotename(object_schema_name(fk.referenced_object_id)) + N'.' + quotename(object_name(fk.referenced_object_id)) as table_name, r.[level] + 1,
  r.[path] + '(' + cast(fk.referenced_object_id as varchar(max)) + ')'
 from
  r join
  sys.foreign_keys fk on fk.parent_object_id = r.object_id
 where
  r.[path] not like '%(' + cast(fk.referenced_object_id as varchar(max)) + ')%'
),
x as
(
 select
  *, row_number() over (partition by table_name order by [level] desc) as rn
 from
  r
)
select
 *
from
 x
where
 rn = 1
order by
 [level], table_name
option
 (maxrecursion 0);

IF OBJECT_ID('[dbo].[Session]') IS NOT NULL
DROP TABLE [dbo].[Session]		
IF OBJECT_ID('[dbo].[UnifiedSession]') IS NOT NULL
DROP TABLE [dbo].[UnifiedSession]
IF OBJECT_ID('[dbo].[ContactObject]') IS NOT NULL
DROP TABLE [dbo].[ContactObject]


CREATE TABLE [dbo].[ContactObject] (ContactObjectId INT IDENTITY(1,1) PRIMARY KEY
                          , Name nvarchar(max)
						  , DeletedTime datetime2(7))



CREATE TABLE [dbo].[UnifiedSession] (UnifiedSessionId INT IDENTITY(1,1) PRIMARY KEY
                           , ContactObjectIdFrom INT FOREIGN KEY REFERENCES ContactObject(ContactObjectId)
                           , ContactObjectIdTo INT FOREIGN KEY REFERENCES ContactObject(ContactObjectId))


CREATE TABLE [dbo].[Session] (SessionId INT IDENTITY(1,1) PRIMARY KEY
                            , UnifiedSessionId INT FOREIGN KEY REFERENCES [dbo].[UnifiedSession](UnifiedSessionId)
                            , ContactObjectId INT FOREIGN KEY REFERENCES [dbo].[ContactObject]([ContactObjectId]))




INSERT INTO [dbo].[ContactObject] (Name,DeletedTime)
VALUES ('ContactObject1',NULL),('ContactObject2', getdate()) 

INSERT INTO [dbo].[UnifiedSession] (ContactObjectIdFrom, ContactObjectIdTo)
VALUES (1,2),(2,1),(2,2),(1,1)

INSERT INTO [dbo].[Session] (UnifiedSessionId,ContactObjectId)
VALUES (1,2),(2,1),(1,1),(1,2)

/* Очистка [dbo].[Session] */
DELETE FROM s
FROM dbo.[Session] s
JOIN dbo.[UnifiedSession] us ON s.UnifiedSessionId = s.UnifiedSessionId
JOIN dbo.[ContactObject] co ON us.ContactObjectIdFrom = co.ContactObjectId
WHERE co.DeletedTime IS NOT NULL

DELETE FROM s
FROM dbo.[Session] s
JOIN dbo.[UnifiedSession] us ON s.UnifiedSessionId = s.UnifiedSessionId
JOIN dbo.[ContactObject] co ON us.ContactObjectIdTo = co.ContactObjectId
WHERE co.DeletedTime IS NOT NULL


/* Очистка [dbo].[UnifiedSession] */
DELETE FROM us
FROM dbo.[UnifiedSession] us 
JOIN dbo.[ContactObject] co ON us.ContactObjectIdFrom = co.ContactObjectId
WHERE co.DeletedTime IS NOT NULL

DELETE FROM us
FROM dbo.[UnifiedSession] us
JOIN dbo.[ContactObject] co ON us.ContactObjectIdTo = co.ContactObjectId
WHERE co.DeletedTime IS NOT NULL


/* Очистка [dbo].[ContactObject] */
DELETE FROM co
FROM dbo.[ContactObject] co 
WHERE co.DeletedTime IS NOT NULL