export all tables in a db without the data

How can I export all the tables in a given database, without the
data.. to later import them elsewhere, with MSSQL7.
Preferably, without having to use the Enterprise Manager -- but even
that would be fine.
ThanksJoe,
A number of options:
http://www.karaszi.com/sqlserver/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Joe Green" <the_3_project@.yahoo.com> wrote in message news:a5b86892.0403310456.b234473@.pos
ting.google.com...
> How can I export all the tables in a given database, without the
> data.. to later import them elsewhere, with MSSQL7.
> Preferably, without having to use the Enterprise Manager -- but even
> that would be fine.
> Thanks|||Hi,
Use the below store procedure using SQL DMO to script out tables from a
database.
CREATE PROC GenerateScript (
@.server varchar(30) = null,
@.uname varchar(30) = null,
@.pwd varchar(30) = null,
@.dbname varchar(30) = null,
@.filename varchar(200) = 'c:\script.sql'
)
AS
DECLARE @.object int
DECLARE @.hr int
DECLARE @.return varchar(200)
DECLARE @.exec_str varchar(200)
DECLARE @.tbname varchar(30)
SET NOCOUNT ON
-- Sets the server to the local server
IF @.server is NULL
SELECT @.server = @.@.servername
-- Sets the database to the current database
IF @.dbname is NULL
SELECT @.dbname = db_name()
-- Sets the username to the current user name
IF @.uname is NULL
SELECT @.uname = SYSTEM_USER
-- Create an object that points to the SQL Server
EXEC @.hr = sp_OACreate 'SQLDMO.SQLServer', @.object OUT
IF @.hr <> 0
BEGIN
PRINT "error create SQLOLE.SQLServer"
RETURN
END
-- Connect to the SQL Server
IF @.pwd is NULL
BEGIN
EXEC @.hr = sp_OAMethod @.object, 'Connect', NULL, @.server, @.uname
IF @.hr <> 0
BEGIN
PRINT "error Connect"
RETURN
END
END
ELSE
BEGIN
EXEC @.hr = sp_OAMethod @.object, 'Connect', NULL, @.server, @.uname, @.pwd
IF @.hr <> 0
BEGIN
PRINT "error Connect"
RETURN
END
END
-- Verify the connection
EXEC @.hr = sp_OAMethod @.object, 'VerifyConnection', @.return OUT
IF @.hr <> 0
BEGIN
PRINT "error VerifyConnection"
RETURN
END
SET @.exec_str = "DECLARE script_cursor CURSOR FOR SELECT name FROM " +
@.dbname + "..sysobjects WHERE type = 'U' ORDER BY Name"
EXEC (@.exec_str)
OPEN script_cursor
FETCH NEXT FROM script_cursor INTO @.tbname
WHILE (@.@.fetch_status <> -1)
BEGIN
SET @.exec_str = 'Databases("'+ @.dbname
+'").Tables("'+RTRIM(UPPER(@.tbname))+'").Script(74077,"'+ @.filename +'")'
EXEC @.hr = sp_OAMethod @.object, @.exec_str, @.return OUT
IF @.hr <> 0
BEGIN
PRINT "error Script"
RETURN
END
FETCH NEXT FROM script_cursor INTO @.tbname
END
CLOSE script_cursor
DEALLOCATE script_cursor
-- Destroy the object
EXEC @.hr = sp_OADestroy @.object
IF @.hr <> 0
BEGIN
PRINT "error destroy object"
RETURN
END
How to execute
EXEC GenerateScript @.server = 'Server_name',
@.uname = 'sa',
@.pwd = 'password',
@.dbname = 'msdb',
@.filename = 'c:\a266uss.sql'
Thanks
Hari
MCDBA
"Joe Green" <the_3_project@.yahoo.com> wrote in message
news:a5b86892.0403310456.b234473@.posting.google.com...
> How can I export all the tables in a given database, without the
> data.. to later import them elsewhere, with MSSQL7.
> Preferably, without having to use the Enterprise Manager -- but even
> that would be fine.
> Thanks