I need to change a table name, how do I change all references to that table in views and sprocs?

Question

question

Ian Roke asked Apr 23, '13

I need to change a table name, how do I change all references to that table in views and sprocs?

I have noticed a few spelling mistakes in tables names on a database that I have taken ownership of. I would like to change this name but also update any views, sprocs, functions etc that refer to this incorrect spelling. Is there a script that I can run to see what changes I need to make?

sql-server-2005 tables

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Apr 23, 2013 at 02:52 PM

@KenJ true. Sometimes the best option is to just put up with the speling mistokes.

1 ·

KenJ commented · Apr 23, 2013 at 02:43 PM

Any application code, ssis packages or reports that might reference these objects? Your dependency web might extend well beyond the database.

0 ·

Ian Roke commented · Apr 23, 2013 at 02:56 PM

Indeed! I'm fairly confident that the errors are only referenced internally as it is a static table. I'll give your code a go @KenJ.

0 ·

Answer 1 · 2013-04-23T14:54:02Z

KenJ answered Apr 23, '13

Check your view/procedure/function/trigger definitions in sys.sql_modules - [ http://msdn.microsoft.com/en-us/library/ms175081(v=sql.90).aspx][1] SELECT * FROM sys.sql_modules AS m WHERE definition LIKE '%misspelt%' Once you have identified and updated the internal references (manually, unfortunately), it might be a good idea to create synonyms for the renamed objects so that any external references don't catch you out - [ http://msdn.microsoft.com/en-us/library/ms177544(v=sql.90).aspx][2] [1]: http://msdn.microsoft.com/en-us/library/ms175081(v=sql.90).aspx [2]: http://msdn.microsoft.com/en-us/library/ms177544(v=sql.90).aspx

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ian Roke commented · Apr 25, 2013 at 10:18 AM

Perfect thanks @KenJ.

0 ·

Answer 2 · 2013-04-23T13:55:33Z

Kev Riley answered Apr 23, '13

If you have the Red Gate SQL Prompt tool, then the "Smart Rename" function will help you.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Apr 23, 2013 at 02:35 PM

Shame. Try the free tool SQLSearch - at least that will enable you to search for references

2 ·

Ian Roke commented · Apr 23, 2013 at 02:00 PM

I'm afraid I don't have access to that tool.

0 ·

Answer 3 · 2013-04-24T11:21:13Z

ruancra answered Apr 24, '13

You can run the code below to find out what Stored Procedures are using those table names, the do a find and replace in each stored procedure. Select * from information_schema.Routines where Routine_Definition like '%YourTablename%'

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question