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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy