Rename/Refactor database elements - only scripts exists but not database

Question

I have script (.sql) files ready to create a database (create schema, procedures, add test data).

I also have coding standards/conventions to be set against those scripts.

Ex: Table name should not end with "_tbl", All the SQL keywords (SELECT, UPDATE, FROM) should be in caps.

Is there a best/Easy way to rename these (according to coding standards I have) such that When I rename a database object, other objects that reference the renamed object should automatically updated with the new name

Found few links to serve this. But would like to confirm is this the best & easy approach. Or any better way than this..

For example, if I rename a column in a table, any stored procedures that reference that column are automatically updated with the new name.

NOTE:Also please comment about tool for MySQL scripts if its different from tool for SQL scripts

Many thanks in advance.....

Answer 1

Changing a column name does not update stored procedures that reference it.

CREATE TABLE t ( v char(1) );
INSERT INTO t SET v='x';

CREATE PROCEDURE p() 
  SELECT v FROM t;

CALL p();
+------+
| v    |
+------+
| x    |
+------+

ALTER TABLE t CHANGE COLUMN v v2 CHAR(1);

CALL p();
ERROR 1054 (42S22): Unknown column 'v' in 'field list'

A similar problem exists for views.

I think the best option is to edit your SQL scripts with a text editor and do search & replace for all instances of the table names you want to change. Update all instances manually with the text editor. Then run the SQL scripts.