Ektron Alias With Folder Name

With assistance from my Ektron Content Folder Names script this script will display all aliases with the folder that the content belongs to.

This is not an ideal process as you can have many folders in Ektron but this can help.

SET NOCOUNT ON

DECLARE @Domain VARCHAR(50)

SET @Domain = '/'

SELECT c.[content_id]
	,c.[content_title]
	,c.[content_html]
	,
	--c.[content_status]
	CASE
		WHEN c.[content_status] = 'A'
			THEN 'Approved'
		WHEN c.[content_status] = 'O'
			THEN 'Checked Out'
		WHEN c.[content_status] = 'I'
			THEN 'Checked In'
		WHEN c.[content_status] = 'S'
			THEN 'Submitted for Approval'
		WHEN c.[content_status] = 'M'
			THEN 'Marked for Deletion'
		WHEN c.[content_status] = 'P'
			THEN 'Pending Go Live Date'
		WHEN c.[content_status] = 'T'
			THEN 'Awaiting Completion of Associated Tasks'
		WHEN c.[content_status] = 'D'
			THEN 'Pending Deletion'
		END AS ContentStatus
	,c.[date_created]
	,
	--c.[content_type],
	CASE content_type
		WHEN - 1
			THEN CAST(content_type AS VARCHAR(5)) + ' - AllTypes'
		WHEN 1
			THEN CAST(content_type AS VARCHAR(5)) + ' - Content'
		WHEN 2
			THEN CAST(content_type AS VARCHAR(5)) + ' - Forms'
		WHEN 3
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Content'
		WHEN 4
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Forms'
		WHEN 7
			THEN CAST(content_type AS VARCHAR(5)) + ' - Library Item'
		WHEN 8
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
		WHEN 9
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Assets'
		WHEN 12
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Media'
		WHEN 99
			THEN CAST(content_type AS VARCHAR(5)) + ' - Non Library Content'
		WHEN 101
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
		WHEN 102
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
		WHEN 103
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
		WHEN 106
			THEN CAST(content_type AS VARCHAR(5)) + ' - PNG Assets'
		WHEN 104
			THEN CAST(content_type AS VARCHAR(5)) + ' - Multimedia'
		WHEN 1102
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archived PDF Assets'
		WHEN 1111
			THEN CAST(content_type AS VARCHAR(5)) + ' - Discussion Topic'
		WHEN 3333
			THEN CAST(content_type AS VARCHAR(5)) + ' - Catalog Entry'
		ELSE CAST(content_type AS VARCHAR(5))
		END AS 'Content Type'
	,
	--c.[approval_method],
	--c.[searchable],
	CASE c.[searchable]
		WHEN 1
			THEN 'Yes'
		WHEN 0
			THEN 'No'
		END AS Searchable
	,CASE
		WHEN uam.[urlaliasnm] IS NULL
			THEN 'No Alias'
		ELSE (@Domain + uam.[urlaliasnm])
		END AS Alias
	,
	--(@Domain + uam.[urlaliasnm]) AS AliasURL,
	--(@Domain + uam.[urltargetnm]) AS ActualURL,
	--c.folder_id,
	--cft.[folder_name],
	cft.FolderPath
--cft.FolderIdPath
FROM [content] c
INNER JOIN [content_folder_tbl] cft ON c.folder_id = cft.folder_id
LEFT OUTER JOIN [UrlAliasMapping] uam ON uam.TargetID = c.content_id
--WHERE c.[content_type] in (101,102,103,104)
ORDER BY cft.FolderPath
	,c.content_id

SET NOCOUNT ON

DECLARE @Domain VARCHAR(50)

SET @Domain = '/'

SELECT AliasName
	,(@Domain + alias_pagename) AS AliasURL
	,(@Domain + alias_logical) AS ActualURL
	,uat.content_id
	,cft.[folder_name] AS Folder_1_Name
	,(
		SELECT folder_name
		FROM [content_folder_tbl] cft1
		WHERE cft1.folder_id = cft.parent_id
		) AS Folder_2_Name
	,(
		SELECT folder_name
		FROM [content_folder_tbl] cft2
		WHERE cft2.folder_id = (
				SELECT cft1.parent_id
				FROM [content_folder_tbl] cft1
				WHERE cft1.folder_id = cft.parent_id
				)
		) AS Folder_3_Name
FROM url_alias_tbl uat
LEFT OUTER JOIN UrlAliasManual_Tbl ON UrlAliasManual_Tbl.aliasid = uat.alias_id
INNER JOIN [content] c ON uat.content_id = c.content_id
INNER JOIN [content_folder_tbl] cft ON c.folder_id = cft.folder_id
ORDER BY folder_name
	,AliasName