I don't like cursors but on a one time thing it will work



DECLARE @Name varchar(50)
DECLARE @sSQL VARCHAR(255)

DECLARE TableCursor CURSOR FOR
select Name
from sysobjects
where xtype = 'U'
OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN


SET @sSQL = 'ALTER SCHEMA anew TRANSFER dbo.' + @Name

EXECUTE @sSQL

FETCH NEXT FROM TableCursor INTO @Name
END

CLOSE TableCursor

DEALLOCATE TableCursor



Thanks,
Neal
-----Original Message-----
From: Apryl Evans
[mailto:mssqldba-ezmlmshield-x12920529.[Email address protected]
Sent: Tuesday, May 29, 2007 4:40 PM
To: LazyDBA Discussion
Subject: Getting rid of dbo when swapping Access db for SQL db

How do I get rid of the dbo extension on the tables in my new SQL db
that I switched from an access DB using the upsizing wizard???
My table look like this:

dbo.table1
dbo.table2
dbo.table3

I want them to look like this:

anew.table1
anew.table2
anew.table3

I can change one table at a time with this SQL statement:

ALTER SCHEMA anew TRANSFER dbo.table1

But there are over 1000 tables, how do I do it in a loop?

Thanks,

Apryl


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html


________________________________________________________________________
_____
Scanned by IBM Email Security Management Services powered by
MessageLabs. For more information please visit http://www.ers.ibm.com
________________________________________________________________________
_____

_____________________________________________________________________________
Scanned by IBM Email Security Management Services powered by MessageLabs. For more information please visit http://www.ers.ibm.com
_____________________________________________________________________________

MS Sql Server LazyDBA home page