Ektron Content List

This is a collection of a few other scripts that I’ve posted here but this script concatenates widget and alias listings into one column so that you only have one row per content item.

SET NOCOUNT ON

DECLARE @Domain VARCHAR(50)

SET @Domain = '/'

SELECT
	--TOP 30
	CASE
		WHEN c.content_type IN (
				1,
				2,
				3,
				4,
				101,
				102,
				103,
				1102
				)
			THEN 'Content Tab'
		WHEN c.content_type IN (
				7,
				106
				)
			THEN 'Library Tab'
		END AS 'Type',
	c.[content_id] AS 'Content ID',
	c.[content_title] AS 'Content Title',
	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] AS 'Date Created',
	(u.first_name + ' ' + u.last_name) AS 'Created By',
	c.[last_edit_date] AS 'Last Edit Date',
	(c.[last_edit_fname] + ' ' + c.[last_edit_lname]) AS 'Last Editor',
	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)) + ' - PDF Assets'
		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)) + ' - MS Office Assets'
		WHEN 102
			THEN CAST(content_type AS VARCHAR(5)) + ' - PDF Assets'
		WHEN 103
			THEN CAST(content_type AS VARCHAR(5)) + ' - PDF Assets'
		WHEN 106
			THEN CAST(content_type AS VARCHAR(5)) + ' - PNG Image 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',
	CASE c.[searchable]
		WHEN 1
			THEN 'Yes'
		WHEN 0
			THEN 'No'
		END AS Searchable,
	CASE
		WHEN (
				rtrim(ltrim(STUFF((
								SELECT ', ' + title
								FROM widget_types wt
								WHERE c.content_html LIKE '%' + wt.title + '%'
								FOR XML PATH('')
								), 1, 1, '')))
				) IS NULL
			THEN 'No Widgets'
		ELSE (
				rtrim(ltrim(STUFF((
								SELECT ', ' + title
								FROM widget_types wt
								WHERE c.content_html LIKE '%' + wt.title + '%'
								FOR XML PATH('')
								), 1, 1, '')))
				)
		END AS 'Widget(s)',
	tt.template_filename AS 'Template Filename',
	cft.FolderPath,
	(
		CASE
			WHEN rtrim(ltrim(STUFF((
								SELECT ', ' + (@Domain + uam.[urlaliasnm])
								FROM [UrlAliasMapping] uam
								WHERE uam.TargetID = c.content_id
								FOR XML PATH('')
								), 1, 1, ''))) IS NULL
				THEN 'No Alias'
			ELSE rtrim(ltrim(STUFF((
								SELECT ', ' + (@Domain + uam.[urlaliasnm])
								FROM [UrlAliasMapping] uam
								WHERE uam.TargetID = c.content_id
								FOR XML PATH('')
								), 1, 1, '')))
			END
		) AS 'Alias List',
	(
		CASE
			WHEN (@Domain + uam.[urltargetnm]) IS NULL
				THEN 'No Alias'
			ELSE (@Domain + uam.[urltargetnm])
			END
		) AS NonAliasURL,
	CASE
		WHEN c.content_type IN (
				1,
				2,
				3,
				4,
				101,
				102,
				103,
				1102
				)
			THEN (@Domain + tt.template_filename + '?id=' + CAST(c.content_id AS VARCHAR(20)))
		WHEN c.content_type IN (
				7,
				106
				)
			THEN (
					SELECT [filename]
					FROM library l
					WHERE c.content_id = l.content_id
					)
		END AS 'View Item'
FROM [content] c
LEFT OUTER JOIN users u ON c.[user_id] = u.user_id
LEFT OUTER JOIN [content_folder_tbl] cft ON c.folder_id = cft.folder_id
LEFT OUTER JOIN templates_tbl tt ON c.template_id = tt.template_id
LEFT OUTER JOIN [UrlAliasMapping] uam ON uam.TargetID = c.content_id
ORDER BY cft.FolderPath,
	c.content_title