How to change table name when script a table from SQL Server SMO

Question

Let's say I have a table "foo" that I want to script out using SMO. Is there a way to have it so that in the generated script the table has a different name such as "foo2"?

Database db = sqlServer.Databases["testdb"];
Table foo = db.Tables["foo"];
foo.Name = "foo2";

If I do this, I get this exception when I try to set foo.Name:

"Cannot perform the operation on this object, because the object is a member of a collection."

Is there any way to do this with SMO?

David Chiew · Accepted Answer · 2012-04-24 02:24:10Z

1

For people like me who are trying to find out how to rename a column using SMO, if you're using the same method as above

foo.Name = "foo2";

you will get the same exception message in this thread. To rename the column you simply need to go

foo.Rename("foo2");

See MSDN for more details:

answered Apr 24, 2012 at 2:24

David Chiew

5946 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Mo · Accepted Answer · 2009-01-16 19:09:15Z

0

You'll have to put the output of the Script method into a string variable and use Replace to substitute the name.

answered Jan 16, 2009 at 19:09

John Mo

1,32610 silver badges14 bronze badges

3 Comments

wnka Over a year ago

Seems risky since the table name might be a substring of other objects (columns, etc)

John Mo Over a year ago

True, but can probably be worked around by FIND "CREATE TABLE foo" REPLACE "CREATE TABLE foo2" to avoid getting into the columns. But then you may have to do multiple replacements to get into constraints and indexes. Not sure if those are scripted separately. Been a while since I played with SMO.

RBarryYoung Over a year ago

The table name will be right after the first "CREATE TABLE " in the script.

Rashmi Pandit · Accepted Answer · 2009-12-11 12:01:47Z

0

You can create an in memory new table with the new name and then script that table.

You can find the code to copy table at Copying a table using SMO

Instead of calling the Create() on the new table, you need to simply call Script().

edited Dec 11, 2009 at 12:01

answered Dec 11, 2009 at 11:56

Rashmi Pandit

23.9k17 gold badges75 silver badges114 bronze badges

Collectives™ on Stack Overflow

How to change table name when script a table from SQL Server SMO

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related