SQL QUERY TO FIND THE NAME OF A FILE IN THE VAULT BASED ON THE LOCATION OF THE .BIN FILE
Posted by Josh Repp (M-Files) on 30 August 2019 05:31 PM
Note: This example is only useful for vaults that use SQL for their database and where the file data for the vault is stored outside of the database itself.
This is useful if, for example, you have a virus scanner that detects a virus in one of the files in your vault. The virus scanner will likely point to file in a location like this...
C:\Program Files\M-Files\Server Vaults\<vaultname>\FileData\0\6\43\64392-4_1.bin
If a virus scanner detects a virus in one of the .bin files that resides in the vault, you will want to destroy that file as soon as possible to avoid spreading that virus if/when that file is opened at a later date. The key piece of information you need from the .bin is the File ID which is part of the path and filename of the file. In the example above, the File ID would be 64392. Once you have your File ID, you can use SQL Server Management Studio to query the vault database and locate the actual title of the file as it appears within M-Files. Using the query below, you simply need to replace the ID in the query with the ID inserted into the value list below. If you have multiple files to look for, you can use a comma separated list of IDs like (11223,44556,77889).
declare @listOfIDs table (id int);
with objs as
select * from objDetails
When the results of the query return, you will see the Object Type ID (document objects are object type '0' by default), Object ID (this is the internal id number that appears on the metadata card of all objects), the Version Number of the object as well as the Title of the object. Once you know the Object ID or the Title of the object in question, you can use that information to search within the vault and locate the file.