format formally case-insensitive code

delux

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.

David Atkinson

Can you give an example of what you want done? Maybe provide a 'before' and 'after' code sample?

Thanks,

David

delux

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

delux

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

David Atkinson

I see. So there's an assumption that all variables are camelcased?

David

delux

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.

delux

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.

David Atkinson

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