In 2011, I blogged about my experiences with the cheap hosting providers like GoDaddy and 1and1 Hosting.  In the blog I mentioned that 1and1 hosting's SQL panel allows you to run SQL DDL statements using dynamic SQL only.  All creates, drops, grants etc. must be issued using EXEC() statements.


This is how a developer creates the sql code:

if object_id('dbo.GetJustDate') is not null
 drop function dbo.GetJustDate
go

create function dbo.GetJustDate(
 @Date datetime) returns datetime 
as
begin
 declare @Ret datetime
 set @Ret=convert(datetime,convert(varchar,@Date,106))
 return @Ret
end
go

grant execute on [dbo].[GetJustDate] to olgauser
go

1and1's SQL control panel expects it in the following way:

exec ('if object_id(''dbo.GetJustDate'') is not null
 drop function dbo.GetJustDate
')
exec ('create function dbo.GetJustDate(
 @Date datetime) returns datetime 
as
begin
 declare @Ret datetime
 return @Ret
end
')
exec ('grant execute on [dbo].[GetJustDate] to olgauser
')

As you can see that converting all your sql code in the dynamic SQL format can be tedious. Well, I have made the job easier for you. My 1&1 Formatter utility would read all your sql scripts from a folder and create an output file in the expected format.

The only requirement is that all your sql statements must be split in separate batches using "Go" statements.  The utility currently supports create procedure, create function, "if object..", and grant statements.  However, you can add support for more statements using the regular expression provided in the config file.

How to use:

1. Download the utility and unzip the contents in any folder.  No installation is necessary.
2. Open the 1and1Formatter.exe.config in a text editor.  Configure the following keys:
   convertTabToSpaces: When > 0, tabs will be converted to as many spaces.
   inFolder: Location of all .sql files needing conversion.
   outFile: Output file title.
   sqlPatterns: Regex pattern to identify the code.  Tweak it carefully.  I have used two named groups, codeBegin and codeBody.  Make sure your changes populate these groups, as they get concatenated in the formatting process.
3. Run the program using Explorer or the command prompt.