February 11, 2004 at 11:47 am
I need a script that will take a 40GB 300+ table database and shrink it to the 1st 1000 rows in each table and delete security tables like tblchargecard. Want to get size to about 1gb to fit on a notebook for development. Any suggestions would be appreciated.
February 12, 2004 at 12:56 am
There's no easy way around it that I can think of. You're going to need an intermediate database that you would then drop the tables that you don't want included and then run through a loop and delete the necessary rows in the appropriate tables. I would then shrink the database, back it up, and move it to your laptop. Obviously, all of this could be setup in a DTS package that could be automated, but I think your initial setup is going to have to be created manually.
February 12, 2004 at 4:55 am
How about a cursor-driven "SELECT TOP 1000 * INTO..." statement. You could script the source DB, remove the CREATE TABLE statements, run the cursor code then use the script to recreate indexes and such. The source_tables cursor would be modified as required to include/exclude any tables.
declare @table_name varchar(100)
declare @sql varchar(100)
declare source_tables cursor for
select name from sysobjects where type = 'U'
and name <> 'dtproperties'
order by name
open source_tables
fetch next from source_tables into @table_name
while @@fetch_status = 0
begin
select @sql = 'select top 1000 * into target_db.dbo.' + @table_name + ' from ' + @table_name
print @sql
execute (@sql)
fetch next from source_tables into @table_name
end
close source_tables
deallocate source_tables
Donhttp://www.biadvantage.com
February 12, 2004 at 9:28 am
Thanks for the help.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply