keep only certain schema tables from a restore

there are 10 schemas in a backup file.. after the restore, though, we want to only the tables from 2 schemas..

after restore, what could be a simple script to drop all the tables that dont belong to those 2 (LTW & PTO) schemas?



thanks
LVL 5
25112Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lcohanDatabase AnalystCommented:
You could build some dynamic code to get all tables not in schema like


--find all tables in schema
select so.name --*
from  sys.objects so inner join sys.schemas ss on so.schema_id = ss.schema_id
where ss.name not in('LTW','PTO')
      and so.type = 'U' -- user table
      

then loop trhough the set and create/execute a DROP TABLE command.
0
lcohanDatabase AnalystCommented:
Here's what the code looks like and it will just PRINT not EXEC the DROPs in order to check that it drops only wanted tables. to do the actual drop uncomment the exec @sqlstr statement and comment the print one above:


declare @table_name sysname,
            @sqlstr varchar(1000)

declare db_cursor cursor for

select so.name --*
from  sys.objects so inner join sys.schemas ss on so.schema_id = ss.schema_id
where ss.name not in('LTW','PTO')
      and so.type = 'U' -- user table

open db_cursor
fetch next from db_cursor into @table_name
while @@FETCH_STATUS=0
begin
 
      set @sqlstr = 'DROP TABLE ['+@table_name+'];';
      print @sqlstr;
      --exec @sqlstr;
      
      fetch next from db_cursor into @table_name
end



close db_cursor
DEALLOCATE db_cursor
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.