Manualy script a table so you can add fields

[MrGoodbyte]

I need to script a table through code so that I can add fields to the table. eg.

If I have a table like this:
Field1 int (Primary Key)
Field2 char(18)
Field3 char(3)
I need to generate a script that looks like this:
Create Table Blah (
Date datetime, /*Part of PK*/
Field1 int, /*Part of PK*/
Field2 char(18),
Field3 char(3),
Name varchar(50),
Status char(1)
)
CONSTRAINT ......I need to script this as well for the Primary Key

Thanks

 

[tlbroadbent]

The best way to create the scripts is throught SQL Enterprise Manager. Right-click a database, select All tasks, select Generate Scripts and create the scripts you want. You can then modify as needed and execute in Query Analyzer. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[rajeevnandanmishra]

Hello MrGoodbyte,

Here is the qyery in which just by replacing MyTableName with your required table you can get an approximate reuired (i think) result.
After running this you just have to concatenate with parentheses and comma e.t.c and can create your required script.

---------------------
SELECT b.name, c.name, b.prec, b.scale,
const=(select g.text from sysconstraints f, syscomments g
where f.constid=g.id and f.id=b.id and f.colid=b.colid )
FROM sysobjects a, syscolumns b, systypes c
WHERE a.id=b.id and b.type=c.type and b.usertype=c.usertype
and a.name='MyTableName'
ORDER BY b.colid

---------------------

I think this will be help you.

 

[MrGoodbyte]

Thanks for your answers. I am aware that you can use the Enterprise Manager, however I am writing a VB program to do this automagically. Users without knowledge of SQL will receive a listing of the tables they want to modify, they will then tick the ones they are to be modified and then Generate and run a script.

Rajeevnandanmishra your post was great and works except that it does not seem to pick for the PK and FK constraints.
Is there another table I need to look at.

 

[calidrake]

For the primary key you can add it to the end of the field when you make it. For example

Create Table Blah (
Date datetime PRIMARY KEY, /*Part of PK*/
.......


Would make that field the primary key. As for foreign key the syntax is:

CONSTRAINT <name of foreignkey> FOREIGN KEY ( <name of column in current table> ) REFERENCES <tablename of referenced table> ( <name column that is referenced> )

The capitalized words are keywords. Just fill in the 'variables' between the <>. So for an example I will use your code

Create Table Blah (
Date datetime PRIMARY KEY, /*Part of PK*/
Field1 int, /*Part of PK*/
Field2 char(18)
CONSTRAINT fk_FIELD2 FOREIGN KEY (Field2) REFERENCES Table2(SomeFIELD),
Field3 char(3),
Name varchar(50),
Status char(1)
)

Don't know what you mean by part of primary key, but I don't think you can have two PKs on SQL databases..unless someone has found some workaround. Oracle allows you (although I wouldn't know why you would want one).

 

[MrGoodbyte]

Thanks Guys, your answers have been great. I have however opted for SQL DMO. Just reference the SQL DMO object and you have the power of SQL Server including Scripting at your fingertips.

What I meant when I said part of the PK was that I had one PK but it was a composite Key comprising of more than one column in the table.

Thanks again
Jonathan Russell (MCSD)