DMO - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DMO

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-07-09 : 06:07:58

nctran writes "As Enterprise manager used DMO to implement its product which has the facility to insert column into an existing table.

I can not find and method to do that in DMO objects. Can you please assist me with this matter.

Thanks."

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2003-07-09 : 14:22:27

If you mean add a column at a specific ordinal position in a table (rather than appending the column to the end of the table), you will see if you select the generate change script button in design view in EM that it actually creates a new table copies the data across and drops the old table then does a rename of the new table to the old table name (it cheats basiaclly

)

HTH
Jasper Smith

0x73656c6563742027546f6f206d7563682074696d65206f6e20796f75722068616e6473203f27

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-10 : 03:20:25

>> generate change script button
save change script.

I used profiler to find out how it did it - never new this button existed.
But then I use e-m slightly more often than cursors.

While I'm there - adding columns to the end - why the transactions?

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.Table1 ADD
z char(10) NULL,
d char(10) NULL
GO
COMMIT

Because it doesn't know what it's going to do - add a primary key and you get an unnecessary copy - note the lock hint!

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Table1
(
s char(10) NOT NULL,
t char(10) NULL,
z char(10) NULL,
d char(10) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (s, t)
SELECT s, t FROM dbo.Table1 TABLOCKX')
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
PK_Table1 PRIMARY KEY CLUSTERED
(
s
) ON [PRIMARY]

GO
COMMIT

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources