locked
Suggestion: Refactor across a whole project RRS feed

  • Question

  • The schema that I am currently working with has over 1000 tables in it. It was created using scripts that have been adapted from oracle scripts. Obviously the person that did the conversion didn't bother about things like best practice and just did a find and replace on the Oracle script until it got to a syntax that SQL Server would accept.

     

    Hence we have the following problems now inherent:

    • Every field that should be an integer is a numeric(8,0)
    • We have VARCHAR(1) fields. This is bad practice - they should be CHAR(1)
    • These VARCHAR(1) fields contain the value "Y" or "N". This would be better represented as a bit field.
    • Loads of others...

    I am not going to go through 1000 tables to search for all occurences of these problems. It would be nice if datadude gave us a way of applying a refactorisation across all the database. Some examples of where this would be useful:

    1. Convert all VARCHAR(1) fields in the project to CHAR(1)
    2. Convert all CHAR(1) fields in the project to BIT
    3. Convert all NUMERIC(8,) fields in the project to INT
    4. Change all columns called [ThisIsAveryLongName] to [ShortName]  (would be very useful where you are trying to maintain consistent naming conventions

     

    Does anyone think this functionality (i.e. applying a refactorisation across the whole project) would be useful? Is it worth requesting at MS Connect?

     

    Regards

    Jamie

     

    Thursday, October 19, 2006 12:37 AM

Answers

  •  Amos Soma wrote:

    Jamie,

    The reason I asked you this question is because when I mentioned the desire to do search and replace, your response to me was:

    'The refactoring functionality currently in datadude already does that for you.'

    Amos.

     

    Maybe I need to clarify.

    You can (for example) make a change to a field name and that change will get propogated to all sprocs, triggers, UDFs etc... that reference it.

    You can only make a single change in isolation though. What you CAN'T do is apply a refactorisation across a whole database.

     

    -Jamie

     

    Thursday, October 19, 2006 8:03 PM

All replies

  • Jamie,

    I think this would be a great feature; could be called datatype conversion. Of course, not specially varchar(1) to char(1) or numeric(8,0) to int, but any allowed type conversion should be allowed. If some conversion is not obvious (like Y/N to bit, how the earth SQL server should know how we would like to convert this), it would be great to give SQL server some kinda conversion policy, even with regular expressions or on a code-based way.

    Don't you think?

     

    regards

    Thursday, October 19, 2006 6:54 AM
  • I agree, the feature would be great.

    Alle

    Thursday, October 19, 2006 8:31 AM
  • Jamie,

    This would be nice but I think to be truly useful, we would also need some sort of search and replace feature in VSTSDBP. Just being able to change datatypes isn't going to be enough I don't think. For example, assume I was able to change every Varchar(1) datatype to Char(1), how do I go about changing all my T-SQL code that contains 'Varchar(1)' and change it to 'Char(1)'? A T-SQL search and replace would be another great addition.

    Amos.

     

    Thursday, October 19, 2006 1:49 PM
  •  Amos Soma wrote:

    Jamie,

    This would be nice but I think to be truly useful, we would also need some sort of search and replace feature in VSTSDBP. Just being able to change datatypes isn't going to be enough I don't think. For example, assume I was able to change every Varchar(1) datatype to Char(1), how do I go about changing all my T-SQL code that contains 'Varchar(1)' and change it to 'Char(1)'? A T-SQL search and replace would be another great addition.

    Amos.

     

     

    Amos,

    The refactoring functionality currently in datadude already does that for you.

     

    -Jamie

     

    Thursday, October 19, 2006 2:09 PM
  • Jamie,

    Could you show me how I might change every occurrrence of say 'Varchar(1)' to 'Char(1)' across all the T-SQL code in my project?

    Thanks - Amos.

    Thursday, October 19, 2006 2:46 PM
  • This probably won't completely meet everyone's needs, but Visual Studio has the Find in Files functionality.

    Go to Edit -> Find and Replace -> Replace in Files

    And set it to look in the entire project. 

    I

    Thursday, October 19, 2006 5:57 PM
  •  Amos Soma wrote:

    Jamie,

    Could you show me how I might change every occurrrence of say 'Varchar(1)' to 'Char(1)' across all the T-SQL code in my project?

    Thanks - Amos.

    You can't do that. That's exactly the kind of functionality that I am asking for in the first post of this thread. Shame no-one form MSFT is replying.

     

    -Jamie

     

    Thursday, October 19, 2006 6:02 PM
  • I think it would be useful to log this in connect. There are lots of ways we can take refactoring and improve upon it in future versions.  Customer feedback as to what would be useful is always good, and having it in connect keeps it in our faces. 

     

    Thursday, October 19, 2006 6:09 PM
  • Jamie,

    The reason I asked you this question is because when I mentioned the desire to do search and replace, your response to me was:

    'The refactoring functionality currently in datadude already does that for you.'

    Amos.

    Thursday, October 19, 2006 6:19 PM
  • I'm aware of the Replace in Files capability but I wouldn't call that 'refactoring'. I think most people would need something a bit more sophisticated.

    Amos.

    Thursday, October 19, 2006 6:22 PM
  •  Amos Soma wrote:

    Jamie,

    The reason I asked you this question is because when I mentioned the desire to do search and replace, your response to me was:

    'The refactoring functionality currently in datadude already does that for you.'

    Amos.

     

    Maybe I need to clarify.

    You can (for example) make a change to a field name and that change will get propogated to all sprocs, triggers, UDFs etc... that reference it.

    You can only make a single change in isolation though. What you CAN'T do is apply a refactorisation across a whole database.

     

    -Jamie

     

    Thursday, October 19, 2006 8:03 PM
  •  tomsmi - MSFT wrote:

    I think it would be useful to log this in connect. There are lots of ways we can take refactoring and improve upon it in future versions.  Customer feedback as to what would be useful is always good, and having it in connect keeps it in our faces. 

     

     

    Thank you Tom. I've done that here: https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=229783

     

    Anyone can click through and add their own comments. As Tom says there are many many directions in which this could go.

     

    -Jamie

     

    Thursday, October 19, 2006 8:13 PM