Copying SQL Stored Procedures (SQL Server 2000)

Hi folks
I am writing a humdinger of a stored procedure, which I can use to
automatically create a second copy of a database, and ensure that the
tables, etc, are all of the same specification.
Here's what I've done so far:
1. Check to see if the second copy of the database exists. If it doesn't
exist, create it.
2. Build up a dynamic SQL string, by using the contents of the sysObjects
and sysColumns tables in the first database.
3. Build up a dynamic SQL string, by using the contents of the sysObjects
and sysColumns tables in the second database.
4. Compare the two SQL strings to ensure that each table in the second
database is an exact replica of the tables in the first database.
By the end of all of this, the result is that the second database contains
exactly the same tables as the first database, with both sets of tables
being identical. The only exception is that the second database doesn't
have any relationships set up between the tables, although that's to come.
So far, so good. However, when I turned my attention to the stored
procedures in the first database, it all went a bit wrong. I can use the
sysObjects and sysComments tables to build up the dynamic SQL from the first
database that would have to be executed against the second database.
However, I've discovered that it isn't possible to create a stored procedure
in any database other than the one you are currently working with. If I
append "Uses <databasename>" at the beginning of the dynamic SQL string, it
then complains that the "CREATE PROCEDURE" command should be the first
command in any batch process.
Can anyone tell me if it's possible for me to do this?
Incidentally, before anyone suggests it, I've never done any DTS stuff
before, so I'm hoping there are other ways of doing it.
TIA
UK_CodemonkeyHave you thought of backing up your database and
restoring it with a new name?|||Ian
DECLARE @.dbname AS VARCHAR(100),@.sql AS VARCHAR(100)
SET @.dbname ='pubs'
SET @.sql ='
CREATE PROCEDURE dbo.nameofSP
AS
SELECT * FROM '+@.dbname+'.dbo.Authors'
EXEC (@.sql)
EXEC dbo.nameofSP
Note: Learn using DTS Packages
"Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
news:drahq7$b7r$1$8300dec7@.news.demon.co.uk...
> Hi folks
> I am writing a humdinger of a stored procedure, which I can use to
> automatically create a second copy of a database, and ensure that the
> tables, etc, are all of the same specification.
> Here's what I've done so far:
> 1. Check to see if the second copy of the database exists. If it doesn't
> exist, create it.
> 2. Build up a dynamic SQL string, by using the contents of the sysObjects
> and sysColumns tables in the first database.
> 3. Build up a dynamic SQL string, by using the contents of the sysObjects
> and sysColumns tables in the second database.
> 4. Compare the two SQL strings to ensure that each table in the second
> database is an exact replica of the tables in the first database.
> By the end of all of this, the result is that the second database contains
> exactly the same tables as the first database, with both sets of tables
> being identical. The only exception is that the second database doesn't
> have any relationships set up between the tables, although that's to come.
> So far, so good. However, when I turned my attention to the stored
> procedures in the first database, it all went a bit wrong. I can use the
> sysObjects and sysComments tables to build up the dynamic SQL from the
> first database that would have to be executed against the second database.
> However, I've discovered that it isn't possible to create a stored
> procedure in any database other than the one you are currently working
> with. If I append "Uses <databasename>" at the beginning of the dynamic
> SQL string, it then complains that the "CREATE PROCEDURE" command should
> be the first command in any batch process.
> Can anyone tell me if it's possible for me to do this?
> Incidentally, before anyone suggests it, I've never done any DTS stuff
> before, so I'm hoping there are other ways of doing it.
> TIA
>
> UK_Codemonkey
>|||Uri
I'll give this a bash. Incidentally, I've been administering SQL Server for
the past 4 1/2 years, and have never needed to get into DTS, mainly because
I've been able to do everything through SQL Stored Procedures.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23x2PuxnIGHA.140@.TK2MSFTNGP12.phx.gbl...
> Ian
> DECLARE @.dbname AS VARCHAR(100),@.sql AS VARCHAR(100)
> SET @.dbname ='pubs'
> SET @.sql ='
> CREATE PROCEDURE dbo.nameofSP
> AS
> SELECT * FROM '+@.dbname+'.dbo.Authors'
> EXEC (@.sql)
> EXEC dbo.nameofSP
>
> Note: Learn using DTS Packages
>
>
> "Ian Henderson" <ianhendersonis@.hotmail.com> wrote in message
> news:drahq7$b7r$1$8300dec7@.news.demon.co.uk...
>