Is there a report or SQL table that lists all files and their size?
I am guessing you are referring to files in the repository?
Hi Blake,
Yes I want to be able to determine the average size and also see how many large files get uploaded.
If it is an electronic file, the size information is kept in the toc table. If it is a native file, the size is stored in the doc table. In the doc table each row is a page, so you would need group the rows by tocid.
Will it have video file size?
A video would be considered an electronic file, so that would be in the toc table.
Do you know if the size is in bytes?
Yes
If you're adding things up across the whole table, don't forget to convert it to bigint (as the columns are int and the sum can easily be over 2 billion).
For ex, aggregate size of all e-docs:
select sum(convert(bigint,edoc_size)) as EDocTotalBytes from toc
Blake this has been very helpful! Is there a table that shows the path?
The path is built from the toc table. Each entry (other than the ROOT FOLDER) has a value in the parentid field that refers to the tocid of another entry, which is the parent folder of the entry. Therefore, you can determine the path by looking at the parent of the entry, and its parent, and its parent, etc. until you get to the ROOT FOLDER entry. The ROOT FOLDER entry should have a tocid=1.
EDIT TO ADD: Here's a recursive query to determine the path of items in the toc table:
--Recursive Common Table Expression to Return List of Entries and Their Paths
WITH recursive_cte AS (
--Anchor query that returns the ROOT FOLDER entry
SELECT
toc.tocid,
toc.name,
toc.parentid,
0 as level,
CAST(toc.name as VARCHAR(MAX)) as path
FROM toc
WHERE parentid IS NULL AND name = 'ROOT FOLDER'
--Recursive Join to Other Entries
UNION ALL
--Recursive query for each entry
SELECT
toc.tocid,
toc.name,
toc.parentid,
level + 1,
CAST(recursive_cte.path + ' > ' + toc.name as VARCHAR(MAX))
FROM toc
INNER JOIN recursive_cte ON recursive_cte.tocid = toc.parentid
--This Break Condition (WHERE Statement) limits the results.
--For this example, it only grabs entries with an entry ID
--of 1000 or lower. Removing this WHERE statement will return
--all entries in the toc table (there could be thousands,
--millions, or even more - use with caution).
WHERE toc.tocid <= 1000
)
--Return all results from the recursive query above.
SELECT * FROM recursive_cte