What's the steps for SQL optimization and changes without reflect live system?

we have a big portal that build using SharePoint 2007 , asp.net 3.5 , SQL Server 2005 .. many developers work in it since 01/2008 and we are now doing huge analysis for current SQL Databases [not share-point DB ] to optimize and enhance it.

• The main db have about 330 table and 1720 stored procedure (SP) created from 01/2008 till now
• Many table names / Columns is very long a开发者_JS百科nd we want to short it
• we found SP names is written in 25 format :( , some of them are very complex and also we want to rename
• many SP parameters need to be renamed
• one of the biggest table is Registered user table, that will be spitted in more than one table for some optimization, many columns name will be changed

I searched for the way that i can rename table names ,columns and i found SQL refactor tool but i still trying it ..

my questions :

• Is SQl Refactor is the best tool for renaming ? or is there any other one ?
• if i want to make it manually, is there any references or best practice for that ?
• How can i do such changes in fast and stable way .. i search for recommendations and case studies if exist ?

This is why people have written coding standards (with defined naming conventions) and have code reviews!! Make sure you implement those procedures right now, to prevent his from getting any worse in the future.

Also for around $300, SQL Refactor™ is an excellent tool. If you were to use search and replace, you'd have countless errors and spend hours and hours editing code. I wouldn't even consider using anything other than SQL Refactor, and would never even try using a manual search and replace method on something as large as you describe.

You can use Visual Studio 2005 Database Edition, 2008 Database Edition or 2010 ultimate to load up your DB schema. This provides refactor capabilities, as well as database "builds" that check references in stored procedures, views and functions to ensure all tables and columns referenced actually exist.