>Hi all
>
>I am working with a remote hosting company. In order to use SMSS I need them to whitelist my IP. That's a pain. The development tools I have can execute a script. Therefore I'm asking for help to build a SQL Server 2005+ sample script to use as a foundation. I will want to alter it anytime and rerun it to have it make changes. Therefore it should do everything conditionally. If a table is not there, create it. If a field is not there, add it. If the field is the wrong data type, alter it. I can and have done such things before, but I would like to make this efficient to support and enhance.
>
>Something like this is a start,
>IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'Customers'))
> CREATE TABLE Customers(Cus_PK int NOT NULL)
>
>but, I would prefer not to duplicate the definition of the pk field above. The next thing I'd like to do after that code is check for the cus_name field and if it's not there, add it and if it's the wrong datatype alter it, etc. Maybe the best thing would be to define a set of variables for each field and call a sproc. Good advice will be appreciated.

One general possibility is to create and maintain a "template" database that has the correct schema, even though it may not contain any data. It should be possible to write generic code that compares the template to the "target" database at the hosting company, and creates a script to "sync" the two.

If you need to modify the target, you just modify the template and let your sync program do its thing.

This strikes me as a not-uncommon task, there may already be code or tools out there to achieve this.