format formally case-insensitive code

deluxdelux Posts: 14
edited April 7, 2010 8:16AM in SQL Prompt Previous Versions
We're moving from a case-insensitive to a case-sensitive SQL 2008 platform. We are not in control of the new platform and need a quick way to do a one-time update to not only keywords (which I know Refactor can already do), but also our own variables. We own Redgate Refactor, Prompt, Compare, and Backup but cannot find a way to auto-format our existing stored procs to match custom code to look like our declare variables.
· Share on Twitter

Comments

  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    Can you give an example of what you want done? Maybe provide a 'before' and 'after' code sample?

    Thanks,

    David
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • deluxdelux Posts: 14
    Yes, my apologies for not doing that originally. This is a really simple example, but basically we have 100s of stored procs that were built in a case-insensitive SQL environment that need to be corrected to work in a case-sensitive environment. It would be great if there was some script that could be run to fix it, but it's not a problem to open each one up and click an auto-format button and ALTER the stored proc. Thanks for your time.

    --original
    DECLARE @Var AS INT

    SET @var = 1234

    SELECT @var = aCol FROM linkedServer.dbo.aTablename --actually looks like this in database LinkedServer.dbo.aTableName

    EXEC dbo.aStoredProc --actually looks like this in database dbo.aStoredPROC

    --auto-format
    DECLARE @Var AS INT

    SET @Var = 1234 --case corrected from @var to @Var

    SELECT @var = aCol FROM linkedServer.dbo.aTablename --case corrected from linkedServer.dbo.aTablename to linkedServer.dbo.aTablename

    EXEC dbo.aStoredPROC --case corrected from dbo.aStoredProc to dbo.aStoredPROC
    · Share on Twitter
  • deluxdelux Posts: 14
    Correction:

    --original
    SELECT @var = aCol FROM linkedServer.dbo.aTablename --case corrected from linkedServer.dbo.aTablename to linkedServer.dbo.aTablename

    --auto-format
    SELECT @Var = aCol FROM LinkedServer.dbo.aTableName --case corrected from linkedServer.dbo.aTablename to LinkedServer.dbo.aTableName
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    I see. So there's an assumption that all variables are camelcased?

    David
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • deluxdelux Posts: 14
    No, camel-casing was just example. It's just basically making case-sensitive any custom code. The variable/stored proc/table name/etc. could have been previously been declare like this @AbCdEFG, and in a case-insenstive environment used later on like this @abcdefg, but I need all references to a variable/stored proc/table name/etc. to match their decleration or definition case.
    · Share on Twitter
  • deluxdelux Posts: 14
    David, did this make sense? This would be really helpful if it's possible using SQL Prompt or Refactor. We have hundreds of existing stored procs that will not compile in our new case-sensitive SQL server without touching each one of them and making all the variables/stored proc/table name references match in case to the objects and declerations.
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,462 Rose Gold 2
    Hi,

    I can log this as a feature request. I'd really like to wait until others have expressed their need for this use case as it's not something that has cropped up much before. If it does, we'll certainly consider it.

    I'd have though you could do something with a perl script that achieves this in the meantime.

    David
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
Sign In or Register to comment.