You'll have to create the schema first though.



Christopher Bellizzi
SQL DBA/Systems Analyst
240 Fall Street
SFO Main Office
Seneca Falls, NY 13148
315-568-7886

-----Original Message-----
From: Neal Sivley
[mailto:mssqldba-ezmlmshield-x65379010.[Email address protected]
Sent: Wednesday, May 30, 2007 8:27 AM
To: LazyDBA Discussion
Subject: RE: Getting rid of dbo when swapping Access db for SQL db

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
________________________________________________________________________
_____


---------------------------------------------------------------------
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



*****************************************************************
This e-mail and any files transmitted with it may be proprietary
and are intended solely for the use of the individual or entity to
whom they are addressed. If you have received this e-mail in
error please notify the sender. Please note that any views or
opinions presented in this e-mail are solely those of the author
and do not necessarily represent those of ITT Corporation. The
recipient should check this e-mail and any attachments for the
presence of viruses. ITT accepts no liability for any damage
caused by any virus transmitted by this e-mail.
*******************************************************************


MS Sql Server LazyDBA home page