Problem solve Get help with specific problems with your technologies, process and projects.

Storing tables with same name in different schema

In Oracle we can store the tables with same name under different schema. How we can achieve the same in SQL Server? I want to store one group of tables under one name and the same group of tables under different name within the same database.

For example, I have two divisions called Accounts and HR, and the database name is Billing:

Database Name: Billing
Schema Name: Accounts
Tables: Dept, Employee, Exp.
Schema Name: HR
Tables: Dept, Employee, Exp.

If I want to refer to them, I can use Accounts.Dept or HR.Dept. In Oracle this type of scheme is called schema. How can I achieve the same functionality in SQL Server, and how can I access the tables?

You can't. SQL Server doesn't have a concept of aliasing or schemas. The closest you can come is to create two groups, assign users to those groups, and then grant access on the objects to the group. Access to the object would not change in the way that you reference it. I'm actually not sure why you would want to duplicate such a thing into SQL Server. It's the same data. Just access the table. The security structure should be transparent to users as well as applications instead of exposed like this is.


For More Information

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.