Go_Search_For_Renamed_Files Query for SOLIDWORKS PDM Professional

In certain situations, files are renamed by mistake and no reference or metadata can be used to locate them. In other cases, the rename is intended but due to new naming conventions, the file may be difficult to find, etc. To help and locate such files more effectively, I have developed a Transact-SQL query and saved as a Conisio Report file (.crp) that can be safely run through the Report Generator feature of SOLIDWORKS PDM Professional: Go_Search_For_Renamed_Files. 

This blog will provide the query to the public as-is, elaborate on the functionality of this query, provide how to use the query, and provide a few use case scenarios/examples. 

Logistics

How do I install and run a .crp file query? 

This blog explains the process of installing and using a report generator file.

Where can I get your Go_Search_For_Renamed_Files.crp file? 

You can download the latest version of this .crp file here.

What if I can’t find my file when it has not been renamed? 

This document is the second part in a series of articles for locating lost files. You can try locating the file using our first query which tackles many common situations that lead to a lost file. The first article in this series can be accessed here.

Can I use the report generator in SOLIDWORKS PDM Standard?

Note that the report generator feature is not included in SOLIDWORKS PDM STANDARD. However, we
are happy to provide our customers the “raw” query to be ran against the PDM STANDARD Database in
SQL Management Studio so your DBA can run the query for you. It is recommended to take a full
backup of PDM prior to running any query. The query can be accessed here.

About the Go_Search_For_Renamed_Files 

The Go_Search_For_Renamed_Files query works like a PDM search. However, it is not bound to the visibility restrictions of set permissions. The query is also free from the limitations of the PDM search cards which, by design, do not show deleted files in the results. It does not return files themselves like a PDM search card does, it is just a table of metadata from the vault database.

It is a very inexpensive query, meaning that it will not use much database server CPU to complete a large search. The query is particularly useful in situations where a file cannot be found through the regular use of PDM search cards. To learn how to interpret and make use of the results of this query, please see “Use case scenarios “ below.

How to use the Go_Search_For_Renamed_Files query 

The search interface of the query is simply an input text box where you can search for a specific file by providing its precise file name with extension, or you can use percent signs “%” as wildcards in place of variant text.

In the screenshot above, %.sld% is used as search criteria. This text means that you want to output information about all files in the entire vault that has the text .sld in the first three characters of the extension and have been renamed. The output results would include but not be limited to .sldasm, .sldprt, and .slddrw files. If you are interested in only Microsoft Excel files, for instance, you could search for %.xlsx or %.xls% if you also need Excel files with macros (.xlsm).

Clicking OK on the search interface of the query will output the flowing information: 

• Document ID (Query is grouped by document ID) 
• Old Name (Matches found based on search criteria) 
• Renamed to (Name that the document was ultimately renamed to)
• DB Local Time (Time rename occurred) 
• Versions (Number of versions of a file) 
• Deleted (Yes or No) 
• Found in (Path to renamed file)

Query Results Example 

Query search criteria used in the example below: “%.%” without quotes.

Note: The file locations are relative to the root folder of the vault. Path of “\” means C:\MyVaultname\. 

It is also worth mentioning that if you are interested in seeing the chronological order that the file was renamed, the best place to view that is in the history of the file. You can navigate to the path provided after locating your file. The “Rename to” column here provides the name that the file with Document ID was ultimately renamed to. In other words, for the file with document ID 2, regardless of its old name, it was ultimately renamed to PawxSpec-XFrame-200.SLDPRT. This means that it is still possible to make sense of the chronological order, but by following this path:

Use case scenarios 

Let’s now gain experience in interpreting the data. Your actual scenario will be different than these scenarios. These scenarios are very “condensed” and made specifically to cover the basics in making use of the query results. We will make use of the example output table above for illustration purposes – refer back to the table as the scenario story progresses. 

A few key things to note are that in all situations, you need to have a certain minimum amount of information to find the file. That is, (a) you know the folder in which you knew it once existed (b) you know a key term, letter, or process that may help: “I know all bearing designs have a ‘B’ at the beginning of their commodity code name so I’ll use B%.sldprt ” (c) you know the exact name you had previously name it, etc. 

If all else fails, just know you can still try to search using metadata as search criteria using the default “Complete Search” search card – stay tuned for a document and query on this topic soon.

Scenario 1: “Wait.. did I do that?” 

Starting with a simple mistake scenario, suppose are looking for file FPV1104Kv4000Data.docx. You search by name in the “Complete Search” search card but only find other files that are named similar to the one you are looking for. Since it’s a .docx file, you may not have metadata that can help locate the file using the complete search card either. You run the Go_Search_For_Renamed_Files query using the exact name of the file you are looking for. You then find that there are two results (see table, Document ID 3). 

The “Renamed to” column contains the name that the file was ultimately renamed to. Therefore, it will show FPV1104Kv5000.docx twice in this column. This means FPV1104Kv4000Data.docx was mistakenly renamed to Potato on 2018-12-30 21:18 and later renamed to FPV1104Kv5000.sldprt. Then, you make sense that in a quick attempt to correct the mistake, the user included a typo in the rename. Having proof that FPV1104Kv5000.docx is indeed the file you are looking for (FPV1104Kv4000Data.docx), you navigate to the path in the “Found in” column to correct the name.

Scenario 2: “What did I name that file?“ 

Suppose you are looking for a part file where you modeled a motor in your engineering sandbox folder. At least you know that’s where you created it and that you copied it out of sandbox and into the engineering folder structure to commit your design into the review process. You search using the Complete Search with the ubiquitous “-test” part of the name which you recall using. However, this yields no search results. 

Running the Go_Search_For_Renamed_Files with search criteria of %.% to search for all files that were ever renamed. The query under this search criteria returns a lot of files that were renamed and you are not interested in the majority. However, you then find in the results that there are two distinct files (Document ID 4 and 5) that have the name “motor-test” one of which existed in your sandbox “\CodeNamePawx\” folder. The file in the sandbox folder was deleted (See Deleted Column) and was the reason why it was not returned when you used the Complete Search. The file with Document ID 5 looks to be a copy of 4 and placed into the Engineering folder structure. The file Motor-test.sldprt that you edited in your sandbox was renamed to X600Kv2400W.SLDPRT and you then use it after a visual inspection/confirmation. 

Tips Please! 

For quick reference, here are the Tips/Hints provided in the description of the query. To view them in the vault, once the query is imported, you can right-mouse-click the name of the query and select properties.

* The file path, version count, and Document ID are returned to help distinguish same-name files. 

* "%.%" without quotes returns all renamed files. 

* Time is in format (YYYY-MM-DD 24Hr) and relative to the database local time. 

* Versions column is the total number of versions of the file returned, it is not the version at which the rename occurred. 

* The name of the user that performed the rename can be found in the history of the file of interest once the file is located. 

* A deleted file can be found in the deleted files folder (A.K.A. PDM Recycle Bin). 

* These results can be exported! Instructions: Installing and Using a Report Generator File.

Related Articles

Working Offline with SOLIDWORKS Tips and Tricks

Test Port Communication Using Windows Telnet Feature

How to Reset the SQL 'SA' User Password