June 21, 2008 at 1:16 pm
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.
June 21, 2008 at 1:49 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 21, 2008 at 5:48 pm
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?
June 21, 2008 at 7:01 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply