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?