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?
 
lcohanConnect With a Mentor Database 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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.