Sometime as a database developer, we might come across changing the schema for the whole database

This is one way to do it; by generating multiple queries in the query editor:

SELECT ‘EXEC(”sp_changeobjectowner @objname = ””oldDBO.’ + name +’ ”” , @newowner = dbo”) ‘

FROM sys.tables

WHERE schema_id = SCHEMA_ID(‘oldDBO’);

Also below is the drop multiple tables by query from the sys.objects.

In this change where is by order date:

SELECT * INTO #tables from sys.objects where create_date > ‘2009-10-27 21:47:20.993’

while (SELECT count(1) FROM #tables) > 0

begin

declare @sql varchar(max)

declare @tbl varchar(255)

SELECT top 1 @tbl = name FROM #tables

SET @sql = ‘drop table ‘ + @tbl

exec(@sql)

DELETE FROM #tables where name = @tbl

end

DROP TABLE #tables;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.