Is it possible to generate alter Table statements using SMO

Hi

I'm trying to modify existing tables in a database.

How can I create alter Table scripts using SMO/DMO

Thank you

Yep, you can use the following to either execute and capture, just execute (which is the default) or just capture the executed commands:

Server s = new Server(".");

s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteAndCaptureSql

//Microsoft.SqlServer.Management.Common.SqlExecutionModes.ExecuteSql

//s.ConnectionContext.CapturedSql.Text; //Get the Text

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks again Jens

I'm trying the follwong code

Server server1 = new Server(".");

Database db= server1.Databases["master"];

server1.ConnectionContext.SqlExecutionModes = SqlExecutionModes.CaptureSql;

foreach (Table Tbl in db.Tables)

{

tabl.Alter ();

}

db1.Refresh();

//writing to a file

writeToFile(server1.ConnectionContext.CapturedSql.Text, "alter", "tables");

But it is not generating Alter statments.

But if I use Create(), in place of alter(), it's generating Create statments.

|||Hi,
if you do not change anything, what are you supposed to see in the ALTER script :-) ?

In this sample I added a column to the table resulting in a script with an ALTER Script and an ADD column command.

Server s = new Server(".");

s.ConnectionContext.SqlExecutionModes = Microsoft.SqlServer.Management.Common.SqlExecutionModes.CaptureSql;

Table t = s.Databases["SMOTest"].Tables["TestTable"];

t.Columns.Add(new Column(t,"SomeSMOTest",DataType.DateTime));

t.Alter();

foreach (string st in s.ConnectionContext.CapturedSql.Text)

{

Console.WriteLine(st);

}

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens

Got it.

while comparing a table in one database to other table (identical) in other database,

if the Source table has some modified(altered) columns and need to be modified in the target table.

How to solve this problem.Any Idea.

I generated the alter scripts manually for each column.

Like

ALTER TABLE [dbo].[wo]

ADD [requested-time] varchar (8 ) NULL

Thank you

|||You will have to do this manually. Load the two schemas and compare the columns (if you just want to check the columns) with each other. Change the columns appropiately with SMO and get the script from the Context. if you want an integrated tool which can do this on its own use Visual Studio for database professionals, this does have a comparer and script generator for keeping the databases in sync.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks alot Jens.

I'll try for this