Smart Rename not finding all instances of item to change

Bodders

I have an Azure SQL Server DB, and am using SSMS 20.1 with SQL Prompt 10.14.12.7945. When I use SQL Search it correctly finds the 4 instances of a field I want to rename (2 tables, 1 sproc, 1 view). However when I use Smart Rename it only finds the single instance in the object selected and nothing else. Has anybody else found this - is it a bug ?
TIA
Bodders

Jon_Kirkwood

Hi @Bodders

 

Thank you for reaching out on the Redgate forums regarding your SQL Search question.

 

Smart Rename should ideally seek out the object you are renaming in objects referenced by it or that reference it to keep dependencies.

The logic for renaming objects can be found here:

https://documentation.red-gate.com/sp/sql-refactoring/refactoring-an-object-or-batch/renaming-objects

 

So I can replicate your scenario to help determine if it is expected behaviour or a possible bug can you share more details on your objects.

 

If you have the CREATE statements for the tables/sproc/view in question or a similar example it will help work through this with you.

I've generated a secure file link if you want to share any sql snippets directly. This link will be valid for 14 days

https://files.red-gate.com/requests/Fyh85FbsylDIsazgZDhrFN

Bodders

Hi Jon - thanks for your response.

Below is the screenshot of the results of my SQL Search returning 4 objects with NumericCode


And here are the results from Smart Rename:

As you can see it has only found one object when it should pick up 4.

Any thoughts on this

Bodders

Jon_Kirkwood

Smart Rename should only rename your selected object where dependencies are found.

 

If you have the same object name in different areas - Table, Procedures, Views etc, and they don't have a dependency defined against them in the create statement linking to the specific object you are renaming, then I would not anticipate they would be renamed.

 

Example below - searching for 'Quantity' in the sample AdventureWorks2019 database. I get hits on multiple results in various object types.

I navigate to the ProductInventory table and conduct a smart rename on column ProductInventory.Quantity > ProductInventory.Quantity2

 

The only dependency on this table is the function ufnGetStock so it is the only other object to be renamed.

 

  

I anticipate this is the displayed behaviour from your provided information.

 

Are you able to check if there are dependencies in your database diagram between the objects you are expecting to be renamed?

If there isn't then I would not be expecting Smart Rename to be able to determine they need to be renamed.

Bodders

Jon - I sent a couple of files to the link you provided. Have you had a chance to look at them yet ?

Jon_Kirkwood

Smart Rename will rename objects where there is a dependency found, e.g. a view references a table - renaming a column in the table will rename the same object in the view. 

SQL Search, in comparison, functions differently, it scans through the CREATE statements of objects for the text string you are entering and returns all matches. 
 
 
With your objects that you are getting results on in SQL Search - are there any that are not being shown in Smart Rename that do have a dependency to the object you are renaming?
e.g. in a stored prod it references a table and thus both are included in the Smart Rename process.
 
If there is no reference and the objects just happen to have the same name, then I would not expect Smart Rename to connect the objects and rename them