>>>Hi all
>>>
>>>I've got an idea and I am looking for suggestions to make it better/easier.
>>>
>>>The premise everywhere seems to be: use a tool to create the tables and define constraints etc. I'm wondering if there is another way to do it: a single script can design/manage a table and no need for a tool.
>>>
>>>If I CREATE TABLE today and tomorrow I have to add a field, I could DROP and CREATE TABLE, or I could ALTER TABLE. Once there are records, I lose the ability to DROP/CREATE TABLE and am forced to only ALTER TABLE. In practice then, the only time I should CREATE TABLE is when the table is not there at all.
>>>
>>>Any column could be resized or dropped and new column(s) could be added anytime.
>>>
>>>So it seems I have to ...
>>>
>>>IF tblX does not exist
>>> CREATE TABLE tblX (dummyfield c(1) not null)
>>>
>>>The dummyfield is needed just to create the table. There's no point adding a real field now, because that may have to be altered, so might as well do it via alter table. That is consistent with the other fields.
>>>
>>>if pkfield does not exist
>>> ALTER TABLE tblX ADD COLUMN pkField uniqueidentifier
>>>
>>>If field1 does not exist
>>> ALTER TABLE tblx ADD COLUMN field1 c(10)
>>>
>>>If size of field1 is not 20
>>> ALTER TABLE tblx ALTER COLUMN field1 ...
>>>
>>>IF NOT EXISTS
>>>(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
>>> AND TABLE_NAME = 'TblX'
>>> AND TABLE_SCHEMA ='dbo' )
>>>BEGIN
>>>ALTER TABLE [dbo].[tblX]
>>> ADD CONSTRAINT [PKTblX] PRIMARY KEY CLUSTERED ([pkField] ASC)
>>>END
>>>
>>>IF NOT EXISTS
>>>(SELECT * FROM dbo.sysobjects
>>>WHERE id = OBJECT_ID(N'[DFTblX_field1]') AND type = 'D')
>>>BEGIN
>>> ALTER TABLE [dbo].[TblX]
>>> ADD CONSTRAINT DFTblX_field1 DEFAULT ('') FOR [field1]
>>>END
>>>
>>>IF dummyfield exists
>>> ALTER TABLE tblX DROP COLUMN DummyField
>>>
>>>One improvement I would like - if possible - is to use the same method for tests. Is it possible to use sysobjects to test for a primary key constraint or is it possible to use information_schema to test for a default field?
>>
>>
>>Why creating the table beforehand with dummy field?
>>What if you don't need that table at all?
>
>Hi Borislav
>
>The assumption I'm going with is the script is to set the database as it should be now. If a table was there and now has to be dropped, obviously we could change the script to drop it.
>
>>
>>BTW you could see what SSMS does when you alter/add a new fields in a table. It first rename the table then create the new one with the same name, add all constraints and insert records from renamed table to the new one and finally drop the old table.
>
>Makes sense. I do not think I would try to do that in such a script. Do you know if it would do that per ALTER TABLE command, or would it batch then all together?

Sometimes ALTER TABLE is PITA :-) esp. when you need to drop some constraint and then create it back (and you don't know the name of this constraint).
Then you should exec all in separate batches with sp_executesql.