How can we help you today? How can we help you today?

invalid objects tool script wrong object name

SQL prompt 10.6.1.17779 ssms version 15.0.18358.0  sqlserver 14.0.3335.7 database at compatibility 110

 I'm using find invalid objects (renaming some databases that cross query) and the displayed create (view) script is giving WRONG names for the views being examined. The bad names are also used in the generated ALTER scripts for the ALTER command, but surprisingly uses the correct name for a later exec sp_addextendedproperty  step.

An example is we have a broken view called dbo.VW_Vein1Ass 
In the invalid objects panel it correctly lists it as VW_Vein1Ass but in the script view at the bottom, it says " CREATE VIEW dbo.VV_Vein1Ass" . Using the script as alter it generates code that says "ALTER VIEW dbo.VV_Vein1Ass" 
Using the ssms script view as alter generates code "ALTER VIEW [dbo].[VW_Vein1Ass]"
This happens on a variety of view messing up the names in different ways, not just the prefix.
In one case it seems to have swapped names from one bad object to another in the script (script uses the name of the next bad object in the script and the next object script has the name of the view above) (corruption within the list of items, not just corruption of the single object name)

Any suggestions?
NSRjecross
0

Comments

1 comment

Sort by
  • Jon Kirkwood
    Official comment

    Hi NSRjecross 

     

    Thank you for reaching out on the Redgate forums regarding your odd SQL Prompt behaviour.

    That initially sounds like SQL Prompt’s Find Invalid Objects feature is getting confused due to name caching or some kind of indexing issue, leading to incorrect object names in the script preview.

    Here are some possible suggestions to clear this variance:

    1. Force Refresh SQL Prompt's Cache
      • In SSMS, go to SQL Prompt → Refresh Suggestions (or Ctrl + Shift + D).
      • This should clear cached object names and reload them.
    2. Manually Verify the Views
      • Run:
        SELECT name FROM sys.views WHERE name LIKE '%Vein%';
      • Check if any unexpected naming inconsistencies exist at the database level.
    3. Check for Ghost Entries in sys.sql_modules
      • If the objects were renamed recently, stale metadata could be an issue.
      • Run:
        SELECT object_id, definition FROM sys.sql_modules WHERE object_id IN (SELECT object_id FROM sys.views);
      • See if any unexpected names appear.
    4. Try SSMS’s Built-in Scripting Instead
      • If SQL Prompt's Find Invalid Objects is unreliable, use SSMS:
        • Right-click the invalid view → Script as ALTER
        • Compare with what SQL Prompt generates.
    5. Check SQL Prompt’s Log File for Errors
      • Logs are usually in %LOCALAPPDATA%\Red Gate\SQL Prompt\Log\.
      • Look for errors or inconsistencies.
    6. Test with a New SSMS Session
      • Restart SSMS and load Find Invalid Objects again.
      • If possible, test on another machine to rule out local corruption.
    Jon Kirkwood

Add comment

Please sign in to leave a comment.