SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_change_table_owner_to_dbo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[sp_change_table_owner_to_dbo]
GO

Create Procedure dbo.sp_change_table_owner_to_dbo
@SpecificUser sysname =  NULL
, @Debug char(1) =  NULL
AS
DECLARE @sql VARCHAR(4000)

/* 
Programmer : David Satz
Description: change all tables and views to be owned by dbo
Parameters : @SpecificUser - if you only want to change 1 users tables and views 
Note: this only works in the current database
*/ 
SET NOCOUNT ON
SET XACT_ABORT OFF 

CREATE TABLE #tables
( TABLE_SCHEMA  sysname
, TABLE_NAME sysname)

SET	@sql = "INSERT #tables SELECT TABLE_SCHEMA, TABLE_NAME " +
" FROM	" + db_name() + ".INFORMATION_SCHEMA.TABLES
WHERE 	TABLE_SCHEMA <> 'dbo'
AND		TABLE_SCHEMA <> 'INFORMATION_SCHEMA'"

IF @SpecificUser IS NOT NULL
BEGIN
	SET	@sql = @sql + " AND TABLE_SCHEMA = '" + @SpecificUser + "'"
END

EXEC( @sql )


DECLARE curTables CURSOR
FOR
SELECT  "exec sp_changeobjectowner '" + TABLE_SCHEMA + "." + TABLE_NAME + "', dbo"
FROM	#tables
ORDER BY TABLE_NAME 

OPEN 	curTables
FETCH	curTables INTO @sql 
WHILE 	@@FETCH_STATUS = 0
BEGIN
	IF IsNull( @Debug, "N" ) = "Y"
		SELECT @sql
	ELSE
		EXEC  (@sql)

	FETCH	curTables INTO @sql 
END

CLOSE curTables
DEALLOCATE curTables
DROP TABLE #tables
SET XACT_ABORT ON
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_change_table_owner_to_dbo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	GRANT EXEC ON dbo.sp_change_table_owner_to_dbo TO PUBLIC
GO