List of images in site

I suggst checking out a product Bizstream created which will do exactly what you want. Out of the box, there is nothing Kentico has which will allow you to get what you are looking for.

http://www.bizstreamtoolkit.com/extensions/search-for-kentico

A crazy complicated ... it will require a lot of research though. Here is the idea: First get all the filenames i.e.:

SELECT  FileName + FileExtension, FileMimeType 
FROM Media_Library (NOLOCK)
JOIN Media_File ON LibraryID = FileLibraryID
WHERE FileMimeType  like '%image%'  or  FileMimeType  like '%pdf%'  
ORDER BY LibrarySiteID, LibraryName, FileName

You need to get all the tables where certain controls are in use, lets say images, files and attachments:

select ClassTableName, Convert(xml,ClassFormDefinition)  from CMS_Class  
WHERE 
ClassFormDefinition like '%<controlname>URLSelector</controlname>%' OR
ClassFormDefinition like '%<controlname>MediaSelectionControl</controlname>%' OR
ClassFormDefinition like '%<controlname>ImageSelectionControl</controlname>%' OR
ClassFormDefinition like '%<controlname>DirectUploadControl</controlname>%' OR
ClassFormDefinition like '%<controlname>DocumentAttachmentsControl</controlname>%' OR
ClassFormDefinition like '%<controlname>FileSelectionControl</controlname>%'

This above will give all the tables where you can have images.

ClassFormDefinition has simple XML syntax like :

  <field allowempty="true" column="ColumnName" .... >
    <properties>
      <fieldcaption>SomeCaption</fieldcaption>
    </properties>
    <settings>
      <controlname>ImageSelectionControl</controlname>
    </settings>
  </field>

you can extract all column names for given controls for given table. It might be a bit complex. I hope you DB is not too big I would rather go via all theses class tables and and manually created a query for each one them to extract the data:

-- most of kentico default ones operate with attachments
-- URL format is  /getattachment/<file GUID>/<filename><extension>
-- for example
SELECT  documentName, NodeAliasPath , attachmentguid, AttachmentName  
from dbo.View_CMS_Tree_Joined 
JOIN CONTENT_File on DocumentForeignKeyValue = fileid 
JOIN CMS_Attachment on AttachmentGUID =  FileAttachment

You might have path to image directly in field if URLSelector is use.

Well I dont know you DB set up, but you might as well check all varchar fields for presence any filename from media library:)

-- delete temp tables just in case
IF OBJECT_ID('tempdb.dbo.#mediaTable', 'U') IS NOT NULL
  DROP TABLE #mediaTable; 
IF OBJECT_ID('tempdb.dbo.#result', 'U') IS NOT NULL
  DROP TABLE #result; 

-- get all library files
CREATE TABLE #mediaTable (MediaFileName NVARCHAR(200), FileMimeType NVARCHAR(20))
INSERT INTO #mediaTable (MediaFileName, FileMimeType )
SELECT  FileName + FileExtension, FileMimeType 
FROM Media_Library (NOLOCK)
JOIN Media_File ON LibraryID = FileLibraryID
WHERE FileMimeType  like '%image%'  or  FileMimeType  like '%pdf%'  

-- declare cursor
DECLARE @BusinessCursor as CURSOR;
DECLARE @COLUMN_NAME as NVARCHAR(200);
DECLARE @ClassTableName as NVARCHAR(200);
DECLARE @IdColumn as NVARCHAR(200);

-- get all possible table into cursor
SET @BusinessCursor = CURSOR FOR
SELECT
(select COLUMN_NAME as IdColumn  from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ClassTableName AND ORDINAL_POSITION =1) IdColumn,
COLUMN_NAME, ClassTableName from CMS_Class  JOIN
INFORMATION_SCHEMA.COLUMNS ON TABLE_NAME =ClassTableName and ( DATA_TYPE like '%varchar%')
WHERE 
ClassFormDefinition like '%<controlname>URLSelector</controlname>%' OR
ClassFormDefinition like '%<controlname>MediaSelectionControl</controlname>%' OR
ClassFormDefinition like '%<controlname>ImageSelectionControl</controlname>%' OR
ClassFormDefinition like '%<controlname>DirectUploadControl</controlname>%' OR
ClassFormDefinition like '%<controlname>DocumentAttachmentsControl</controlname>%' OR
ClassFormDefinition like '%<controlname>FileSelectionControl</controlname>%';


-- declare reustl table 
CREATE TABLE #result ([FileName] NVARCHAR(200), ClassTableName  NVARCHAR(200), ColumnName NVARCHAR(200), IdColumn NVARCHAR(200), IdColumnValue int)

OPEN @BusinessCursor;
FETCH NEXT FROM @BusinessCursor INTO @IdColumn, @COLUMN_NAME, @ClassTableName;
-- loop
WHILE @@FETCH_STATUS = 0
BEGIN
 PRINT @COLUMN_NAME + ' ' + @ClassTableName;

 EXEC('insert into  #result (FileName, ClassTableName, ColumnName, IdColumn, IdColumnValue) 
 select MediaFileName,''' + @ClassTableName + ''',''' + @COLUMN_NAME + ''', ''' + @IdColumn + ''', ' + @IdColumn + ' 
 from ' + @ClassTableName + ' join #mediaTable on ' + @COLUMN_NAME + ' COLLATE DATABASE_DEFAULT like ''%'' + MediaFileName + ''%'' COLLATE DATABASE_DEFAULT')
 FETCH NEXT FROM @BusinessCursor INTO @IdColumn, @COLUMN_NAME, @ClassTableName;
END

CLOSE @BusinessCursor;
DEALLOCATE @BusinessCursor;

select * from #result

I am not sure your answers are going to help me.

Brenden Kehren: I will look at the tool after our next release, but it looks to only retrieve info for one searched item at a time. Peter Mogilnitski: Testing out your query now but, it looks like my DB may be a bit too large for it to run efficiently, also it just seems to be looking for the locations in the DB.

What I am looking for is essentially a way to map where in the sites the images and PDF's are presented, so the output would look something like:

Site Path media a /home pic1.jpg a /home pic2.jpg a /junk junk.pdf b /home pic1.jpg b /home pic2.jpg

etc... For some of the media I have somewhat figured things out, but for items that are being used as navigation images I cannot figure out how they get linked back to the page they get used on.

The Search tool will help. You can add multiple conditions to the search. For example:

Look in:

• page templates
• pages

For these paths:

• /getmedia*
• /getattachment*
• /medialibrary*

Typically images are stored in the media library. You will either get the image with a relative url path (/medialibrary which is the codename of the site) or with other methods based on the way the file is stored in the page (could be an attachment or a file upload).

I'd suggest standing up a fresh Kentico install and adding the Search tool to it. Test it out and see if it does what you are looking for before you install it on your project. If you do or don't like it, simply delete the project and the database and you're done.

My query is meant to be run only once. it is inefficient, and yes it checks all varchar fields for presence of media library filename :) it might take sometime if DB is big but you run it once to make the report.