Copy database structure using sysobjects

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

Transact-SQL (2000)

Copy database structure using sysobjects

Author

Topic

Frances
Starting Member

20 Posts

Posted - 2005-02-18 : 07:32:43

Hi all,

I'll refrase my problem... Sorry for the inconvenience!

What I want to do is copy the complete database structure (tables, stored procedures, views, functions etc) from one database to another. Perhaps generate a script first that I can execute? The script would have to look something like this:

CREATE TABLE [dbo].[TableOne] (
	[FK_KeyOne] [int] NOT NULL ,
	[FK_KeyTwo] [int] NULL ,
	[KeyThree] [varchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TableTwo] (
	[FK_KeyOne] [int] NOT NULL ,
	[FK_KeyTwo] [int] NULL ,
	[KeyThree] [varchar] (100) NULL ,
	[KeyFour] [varchar] (50) NULL ,
	[KeyFive] [varchar] (50) NULL ,
	[KeySix] [bit] NOT NULL 
) ON [PRIMARY]
GO

create procedure ProcedureOne
@variableOne		int,
@variableTwo		text
as
insert into TableOne(variableOne, variableTwo) values (@variableOne, @variableTwo)

GO

Just like the script that you get in Enterprise Manager when you say "Generate Script"

Any clues?

Thank you

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-18 : 08:05:27

set s = CreateObject("SQLDMO.SQLServer")
s.LoginSecure = True
s.Connect "SqlServerName"
set t = CreateObject("SQLDMO.Transfer")
t.CopyAllObjects = True
s.Databases("myDB").ScriptTransfer t, 2, "D:\myDBscript.sql"

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 10:58:27

stoad, You start to freak me out with all this SQLDMO and vbscript stuff

Cool.
And you're typing it faster than it takes me to read it...

rockmoose

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 11:06:42

[url]http://www.sqlteam.com/item.asp?ItemID=17320[/url]

rockmoose

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-18 : 15:10:50

rocko!

it's IntelliSense not me (I used to write it in a ms access module (with
checked reference to SQL DMO Library)).

... and I forgot:

s.Disconnect
Set t = Nothing
Set s = Nothing

Frances
Starting Member

20 Posts

Posted - 2005-02-21 : 05:50:40

Thanks all, it works!

*Party, party*

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources