Solved

SQL Server Database Import - Schema Name Wrong

Posted on 2010-08-31
11
415 Views
Last Modified: 2012-05-10
First of all, I'll admit I don't know much about SQL Server.

I made a backup of our SQL 2005 database at Network Solutions and had our new webhosting company import the backup. When I connect to the server using SQL Server Management Studio 2008, I can see that each of my tables are prefixed like this: OldDataBaseName.tblTableName. This is going to cause problems in all of my queries.

The new database is named something slightly different than the old one. That's probably why this happened in the first place.

What's the best way to resolve this problem?
0
Comment
Question by:HKComputer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 33568696
You can modify the schema of your objects using a system SP - the attached SQL script will modify all objects from @OldOwner to @NewOwner
declare @OldOwner varchar(100) declare @NewOwner varchar(100) 
set @OldOwner = 'OldOwner' set @NewOwner = 'NewOwner'
 
select 'sp_changeobjectowner ''[' + table_schema + '].[' + 
table_name + ']'', ''' + @NewOwner + ''' go 
 from information_schema.tables where Table_schema = @OldOwner

Open in new window

0
 
LVL 6

Expert Comment

by:anushahanna
ID: 33569540
the above logic is good. there is some syntax error, you can clear up.

I have corrected the syntax here:

DECLARE @OldOwner varchar(100) , @NewOwner varchar(100)
SET @OldOwner = 'OldDataBaseName'  
SET @NewOwner = 'dbo' -- or New Schema Name
SELECT 'sp_changeobjectowner ''[' + table_schema + '].[' +table_name + ']'', ''' + @NewOwner + ''''
FROM information_schema.tables
WHERE Table_schema = @OldOwner
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 33569550
The above is for tables - if you see the ownership problem for procs, then you would need to hit sysobjects

DECLARE @OldOwner varchar(100) , @NewOwner varchar(100)
SET @OldOwner = 'OldDataBaseName'  
SET @NewOwner = 'dbo' -- or New Schema Name
SELECT 'sp_changeobjectowner ''[' + u.[Name] + '].[' +o.[Name] + ']'', ''' + @NewOwner + ''''
FROM dbo.sysobjects o
INNER JOIN dbo.sysusers u
ON o.uid = u.uid
WHERE o.Type = 'P'
AND u.[Name] = 'OldDataBaseName' -- Change OldDataBaseName's Procs to DBO's (or the new schema of choice)
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 4

Author Comment

by:HKComputer
ID: 33569561
Thanks, we only need to update the tables. We aren't using any stored procedures. Yet.
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 33569764
OK. Then that script alone should take care of you.

Once those scripts are generated, just copy paste, run one at at time or a bunch at a time, to make sure you are following what is going one or where the error is.

if it there are 100s of them, then you can run them together, and if you do not need to track minutely, then run them all and make sure there are no errors.
0
 
LVL 4

Author Comment

by:HKComputer
ID: 33583689
OK, I don't know why but the code above didn't work. It ran without errors but it didn't change the schema. The database is a SQL Server 2008 database. Could that have been part of the problem? From what I'm reading, the code recommended above appears to be deprecated.

I ended up using the following code:
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.TableName

0
 
LVL 6

Accepted Solution

by:
anushahanna earned 500 total points
ID: 33585999
hmm... in 2005, it works fine. But you are right: it is the right thing to use the ALTER statement instead..

the attached code will script it out for you.
DECLARE @OldOwner varchar(100) , @NewOwner varchar(100)
SET @OldOwner = 'OldDataBaseName'  
SET @NewOwner = 'dbo' -- or New Schema Name
SELECT 'ALTER SCHEMA '+@NewOwner+' TRANSFER '+ '[' + table_schema + '].[' +table_name + ']'
FROM information_schema.tables
WHERE Table_schema = @OldOwner

Open in new window

0
 
LVL 4

Author Comment

by:HKComputer
ID: 33587707
And I won't say for sure that it won't work in 2008. You'd think if it was totally deprecated I would have gotten an error message and I did not. Instead I got a recordset that contained the schema names and table names but nothing got updated.
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 33589090
HKComputer, actually, the documentation says "This stored procedure only works with the objects available in MicrosoftSQL Server 2000"; but it seemed to work in 2005 for me.

can you please try the below code in SQL 2008 and see if it works for you in the test scenario. it will create a test database and them remove it at the end so you should not have to worry anything about it.
code.txt
0
 
LVL 4

Author Comment

by:HKComputer
ID: 33589138
I'm sorry but my shared hosting account doesn't give me permissions to create a database. I did try the script but here's the output:

Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.
Msg 911, Level 16, State 1, Line 1
Database 'pztestdb' does not exist. Make sure that the name is entered correctly.

(1 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored procedures.

(1 row(s) affected)
Caution: Changing any part of an object name could break scripts and stored procedures.
Msg 3701, Level 11, State 1, Line 6
Cannot drop the database 'pztestdb', because it does not exist or you do not have permission.
0
 
LVL 6

Expert Comment

by:anushahanna
ID: 33609112
OK. No problem. Can you create a table? - then pl try this..
IF PERMISSIONS()&2=2

create table dbo.testtable (testcol smallint)
select schema_name(schema_id) from sys.all_objects where name = 'testtable'
create user testuser without login
GO
create schema testuser AUTHORIZATION testuser
GO
exec sp_changeobjectowner '[dbo].[testtable]', 'testuser'
select schema_name(schema_id) from sys.all_objects where name = 'testtable'
exec sp_changeobjectowner '[testuser].[testtable]', 'dbo'
drop schema testuser 
drop table dbo.testtable 

ELSE

   PRINT 'ERROR: No permissions to create a test table'

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Better way to make a query with date filter. 5 43
shrink table after huge delete 2 28
Many to one in one row 2 40
How to add an Index to a date/time field? 15 13
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question