Ektron – Find DMS Content File

Sometimes you’re asked where a DMS file exists on the server and have to track it down, this query should help.

Caveat: It helps to know where your root/main asset directory is on your server.

SELECT content_id AS "Content: Content ID"
	,content_title AS "Content: Content Title"
	,(storage + '.' + RIGHT(NAME, Len(NAME) - Charindex('.', NAME))) AS "AssetDataTable: Storage"
	,pubFolderPath AS "AssetDataTable: Folder"
FROM content
INNER JOIN AssetDataTable ON id = asset_id
WHERE content_id = 87341

SELECT c.content_id AS "Content: Content ID"
	,c.private
	,c.searchable
	,c.content_title AS "Content: Content Title"
	,(storage + '.' + RIGHT(NAME, Len(NAME) - Charindex('.', NAME))) AS "AssetDataTable: Storage"
	,adt.version
	,adt.[dateModified]
	,cft.[folderpath]
FROM content c
INNER JOIN AssetDataTable adt ON id = asset_id
INNER JOIN content_folder_tbl cft ON c.folder_id = cft.folder_id
WHERE cft.[folderpath] LIKE '%<folder name>%'
ORDER BY cft.[folderpath]
	,c.content_title

SET NOCOUNT ON

DECLARE @Domain VARCHAR(50)

SET @Domain = '/'

SELECT c.content_id AS "Content: Content ID"
	,c.content_title AS "Content: Content Title"
	--  ,(storage + '.' + RIGHT(NAME, Len(NAME) - Charindex('.', NAME))) AS "AssetDataTable: Storage"
	,[handle] AS [DMS File Name]
	,CASE
		WHEN uam.[urlaliasnm] IS NULL
			THEN 'No Alias'
		ELSE (@Domain + uam.[urlaliasnm])
		END AS Alias
	,[mimeType]
	,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 - MSWord'
		WHEN 102
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets - PDF'
		WHEN 103
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets - PDF'
		WHEN 1102
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets - PDF'
		WHEN 104
			THEN CAST(content_type AS VARCHAR(5)) + ' - Multimedia'
		WHEN 106
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets - Image'
		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'
	,adt.version
	--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]
	,adt.[dateModified]
	,cft.[folderpath]
	--,c.private
	,CASE c.[private]
		WHEN 1
			THEN 'Yes'
		WHEN 0
			THEN 'No'
		END AS [Private]
	--,c.searchable
	,CASE c.[searchable]
		WHEN 1
			THEN 'Yes'
		WHEN 0
			THEN 'No'
		END AS Searchable
FROM content c
INNER JOIN AssetDataTable adt ON id = asset_id
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 cft.[folderpath] LIKE '%<folder name>%'
--WHERE [mimeType] not like '%pdf%'
ORDER BY mimeType
	,cft.[folderpath]
	,c.content_title