April 17, 2009 at 8:06 am
I have a production database and I want to create the same structure for a development version of the same database. The tables in the production database changes everyday, not all tables, but some. I wanted to do a script or something that copies the structure in the development database on a weekly basis.
I thought of doing something like this :
SELECT table_name
INTO tbl_ToCopy
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
FOR EACH TABLE In tbl_ToCopy
SELECT *
INTO Dev.table_name
FROM tbl_ToCopy.table_name WHERE (0=NULL)
NEXT
The "WHERE (0=NULL)" part is because I just want the structure, any quicker or wiser ideas?
Thank you
April 17, 2009 at 8:41 am
Are you saying the structure of your production databases changes daily? :w00t: That sounds a little scary. Maybe you should look into something like doing a backup of prod and restoring to dev. It would wipe out your dev data but you would have an exact copy of the structure daily. Just a thought. I am sure there is somebody with a way to do what you want but at least this would give you the desired result.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 17, 2009 at 8:48 am
Well, I don't think tables change structure everyday, but there's at least a new table every week.
The primary goal is to get the same structure without all the data because de production database is quite big and I don't need all the data in dev, it's just wasted space if I keep all data in dev database.
A database restore without the data? Didn't find any ways to do it...
April 17, 2009 at 9:17 am
There is certainly a better way to do this but you could probably use the
sys.tables where create_date > @LastSyncDate or modify_date > @LastSyncDate
to get a list of tables.
Then create a script by utilizing sysobjects and syscolumns. It would painful to build the script because i don't know enough about how to create the script to handle all the foreign keys, datatypes and such. If you run profiler when you script an object to a new window you can get an idea of how much code is behind generating those scripts.
Of course Redgate has a neat tool called sql compare that as I remember will generate scripts for missing objects. (I haven't used that tool in a few years since the place I work now won't spring for it)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply