Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create Schema Alias References Another Schema Expand / Collapse
Author
Message
Posted Saturday, June 21, 2008 1:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 20, 2009 12:05 PM
Points: 8, Visits: 9
I wanted to know if/how I can create a schema alias that references another schema name such that I can get tables from the original schema alias. The reason I ask is that I have a SS2K DB that is case-sensitive. But I need to do insensitive queries against it. So I was hoping there was a way to create an uppercase schema name that can be used to get a list of tables from the original case-sensitive schema name.

For example,

If I have a schema called dbo, such that select * from dbo.USERTABLE returns a list of columns

I want to be able to create another schema called DBO such that select * from DBO.USERTABLE will return the same result.

I've spent two days straight trying to come up with something, so any and all help would be greatly appreciated.

I need to get this done, so if there are other possibilities to accomplish this, I am open to suggestions.

Thanks in advance.
Post #521304
Posted Saturday, June 21, 2008 1:49 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
Not automatically. I think that you would have to create Views/Synonyms in your CI schema to map to the objects in your CS schema.

Plus, I don't think that Case-Sensitivity or collation is a schema attribute, so you might have to put the CI schema in a different database.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #521308
Posted Saturday, June 21, 2008 5:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 20, 2009 12:05 PM
Points: 8, Visits: 9
Okay, so if I create a seperate database with a seperate schema, can I make calls to the original database and CS schema? Is there some view/procedure I can create that will give me all tables in the CI database from the CS database? Also, do you know of a way to filter all database requests for a particular database through a procedure to begin with? Perhaps some sort of translator that when a user attempts to connect to the database from a JDBC connection, they will go through the procedure first?
Post #521346
Posted Saturday, June 21, 2008 7:01 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:54 AM
Points: 9,294, Visits: 9,492
Well a Veiw will filter all queries through its SELECT statement. If you need to filter it through a procedure, then you woul dneed the view to SLEECT from a table-valued function (not inline) instead.

Honestly, I have never dealt with a CI to CS situation before, so I cannot advise you much. I have, however, done projects for customers where I automated the generation of views in one databas for all of the tables in another.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #521351
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse