Tsql Stored Proc Rewriting 06.09.08
OBEJCTIVE:
REWRITE TSQL STORED PROCEDURES OF 2 database, one stagging and one production database.
The production database contains 22 tables, 61 stored produres, 8 functions totalling 8000 words.
The stagging database contains 37 tables, 140 stored procedures, totalling 20000 words.
Most of the stored procedures (90%) are duplicates where only one line changes in the code.
Most of the table (80%) have the same structures but different names just because they are
resulting the imports of files with different names but the same structure.
TASKS TO PERFORM:
—————–
1. Immediate action (3 to 5 hours).
Write scripts to simplify the table structures in stagging and drop the useless tables.
80% of the table and the stored procedures are redundant.
2. Simplify the ETL scripts to transfer data from the stagging database to the production
database. Make a unique script for all ETL import scripts. (2 to 3 days).
3. Replace 3 tables that represent a hierarchic structure into a single table in the production
database. Write the migration scripts, the impacts on other stored procedures.
Write Views to retrieve data from one level of the hierarchy.
(1 to 2 days)
4. Create a table and stored procedure nomenclature, and apply it to all items in the databases.
(2 to 3 days).
4. Write a documentation about all tables, stored procedures and views.
DELIVERABLES:
————–
1. TSQL scripts to regenerate the datbase + stored procedures.
2. Word documentation about all the stored procedures and the table.
PROCESS:
——–
1. I deliver the TSQL scripts to create the existing tables, stored procedures.
I include 5% of the database data since it’s too huge and also it’s confidential.
2. You start the work and ask any required information about the role
of each stored procedure if not clear.
3. You deliver the work, I test it on an existing website to test
everything is still working, and pay you.
