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
