Yes, I want refactoring in TSQL/SQL Server. What do I mean by Refactoring in SQL?
Specifically, I want to be able to evolve my database schema without breaking the application. For me at least, I frequently evolve the naming of my database schema as I’m adding features to deployed apps, but it is really hard to know for sure that I’ve updated everything that depends on those changes.
I have seen tools that generally hunt down all the references to tables, views, stored procedures, fields, etc. within SQL Server objects and update them for you. Some tools even look outside in VB.NET or C# code. But those techniques are, by their very nature, incomplete. I mean, what about dynamic SQL?
So what do I think is needed?
The first needed feature would be aliases for SQL Server objects. If I could define an alias for any SQL Server object such as a table, view, stored procedure, field, etc and have it behave when referenced exactly as the object it aliased, I would have part of what I need. For example, I envision you could define a alias called [Item] on the table [Inventory] like so:
CREATE TABLE ALIAS Item FROM Inventory
Then the following two SQL statements would be identical in function:
SELECT * FROM Inventory
SELECT * FROM Item
As would these SQL statements:
UPDATE Inventory SET Price= Price * 1.01
UPDATE Item SET Price= Price * 1.01
The second feature required would be the ability to tag an object as having all accesses to it logged:
ALTER TABLE ALIAS Item LOG ACCESS
Once you tagged a SQL Server object with “log access”, SQL Server would write a record to a special log table every time that object was touched in any way. Here is a potential Access Log Structure:
CREATE TABLE AccessLog
If you note some of the fields in my hypothetical access log you’ll see data that currently does not exist to be logged, such as ConnectionName and ExecuteID. A third “nice to have” feature would be the ability to give unique IDs and unique names in VB.NET and C# code where a connection is made and every place in code where a SQL statement is executed just like providing error numbers in error handlers.
With the first two features I could rename any SQL Server object, create an alias to that object using its old name, and then mark the alias to be logged. Then I could run my app, watch the log file, and see if I missed any references to the old names. If so I could easily use the logged data to track down the places I missed, especially if I had the third feature that allowed me to annotate ADO.NET calls with reference information. I could even deploy an app and let it run for a month or two while monitoring the access logs to make sure I didn’t miss anything.
I sure do wish SQL Server had those features. Don’t you?