Create Same Tables in Multiple Databases - SQL Server 2005 SSIS

[dkillilea]

I'm trying to set up a data warehouse in SQL Server 2005.

There's various methods, here's mine.

I'd like to have a database for every month.

Within each database I will have the same tables.


MY QUESTION:

I'd like to create a seperate Execute SQL task that creates
1. the Databases and
2. the Tables within each database. (Same for all databases)

I did step 1 easily by injecting a CREATE DATABASE script into the Execute SQL task in SSIS. No problems there.

My databases are labeled YEAR_001_01, YEAR_001_02 ...YEAR_003_12

This gives me 36 months of activity to work with. I know it's bulky, but bare with me...

** Step 2. is the problem. I want the Execute SQL Task to first search all databases to see if they are LIKE 'YEAR_00%' and if so, place all the tables inside each database labeled as such.

I'm really not an expert at making this happen. Any help would be appreciated. I've aready spent 20 hours attempting this to no avail. I'm an expert with MS Access so as soon as I get my basic structure arranged I'll be in Access/SQL heaven again. please help!!!

I just want to create tables here, not loading them with data yet.

Thanks,
Doug

 

[druer]

Are you looking for the SQL to find the right databases, or the sql to create the tables?

Another option is to create the tables you want in the MODEL database, so that each time you create a new DB it will already get the table schemas you have defined.

Hope it helps,
Dalton

PS - Not liking the design method, but if you've researched your options and thought through how you are going to write queries to pull data for reports from this schema and you've selected this and eliminated the other options then have at it.

 

[dkillilea]

Yes and Yes. I need SQL to find the databases starting with YEAR_00 and SQL to create the same tables in each of those databases.

PS- Thanks for your input on the design method. I was expecting some criticism and it's well taken. I'm reading everything under the sun and taking the SQL Server 2005 lab in Dallas in two weeks. Your help is greatly appreciated.

Thanks,
Doug

 

[druer]

If you don't want to use the MODEL database approach so that the databases are created with what you need ...

1. You can do a select of master..sysdatabases to find databases with a name like you've identified and put that into a cursor to loop through:

select * from master..sysdatabases where name like 'Year%'

2. An easier approach is to use the undocumented stored procedure call sp_msforeachdb. Which iterates all of the databases for you and executes the command you give it:

exec master.dbo.sp_MSforeachdb '..your command goes here..'

All you would have to do is wrap the command (inside) with a check for the database name. (The ? is the name of the database that is currently in the cycle)

'if ? like ''Year%'' begin use ? all_of_your_stuff end'