|
|
Posted by louisyoung187@hotmail.com on 11/02/06 06:47
> Does anyone have a cunning plan? In a nutshell, I would like to be
> able to:
>
> 1. Create a new database with a derived name - as in the case below a
> name based on the year, though it might be month as well.
>
> 2. Create and populate tables in this new database.
>
Well, if you are creating databases on the fly then there is probably
some aspect of your design which needs to be relooked.
Basically the USE clause is only in effect for as long as the EXEC
procedure is in scope, so you'll need to place all the statements in
the EXEC procedure, but I would rather recommend you pop this into a
stored procedure in a static database and simply pass the stored
procedure and the DBName as a parameter , e.g.: EXEC('sp_dostuff ' +
@DBName). Anyway, here's how could've done it. Hope this helps:
USE MASTER
DECLARE @DBName VARCHAR(123)
SET @DBName = 'MyTest_' + CAST((Year(Getdate())) AS Varchar)
if not exists(select dbid from master.dbo.sysdatabases where name =
@DBName)
exec('CREATE DATABASE ' + @DBName)
else
raiserror('Database already exists.',3, 1)
EXEC ('USE ' + @DBName +
' if exists (select * from dbo.sysobjects where id = ' +
'object_id(N''[dbo].[TestTable]'') and OBJECTPROPERTY(id,
N''IsUserTable'') ' +
'= 1) ' +
'drop table [dbo].[TestTable] ' +
'CREATE TABLE [dbo].[TestTable] ( ' +
' [TestID] [int] NOT NULL , ' +
' [Description] [varchar] (50) COLLATE Latin1_General_CI_AS NULL
' +
') ON [PRIMARY] '
)
Regards,
Louis
Navigation:
[Reply to this message]
|