Summary

Find filenames longer than a chosen length threshold. Long filenames frequently cause problems during migrations between platforms (for example, Open Enterprise Server to Windows Server), with backup tools, and with downstream applications that enforce path-length limits.

Details

This query assists in finding any file names that exceed as specific length, in this case 128 characters.

You can change the length threshold by editing line 12, and you can limit the scope by using any of the common scoping techniques found in the Custom Query Guide.

Code

SQL Server

SELECT
    sd.fullpath,
    LEN(sd.fullpath) AS fullpath_length,
    sd.name,
    LEN(sd.name) AS filename_length,
    sd.size,
    srs.byte_string(CAST(sd.size AS BIGINT)) AS size_string,
    sd.scan_target
FROM
    srs.current_fs_scandata AS sd
WHERE
    (LEN(sd.name) > 128) AND
    (sd.path_type = 1)
ORDER BY
    filename_length DESC

PostgreSQL

SELECT
    sd.fullpath,
    length(sd.fullpath) AS fullpath_length,
    sd.name,
    length(sd.name) AS filename_length,
    sd.size,
    srs.byte_string(CAST(sd.size AS BIGINT)) AS size_string,
    sd.scan_target
FROM
    srs.current_fs_scandata AS sd
WHERE
    (length(sd.name) > 128) AND
    (sd.path_type = 1)
ORDER BY
    filename_length DESC