[RESOLVED] [dbo] vs [erp] tables

[kebo]

Hi all,
This is a question out of curiosity mostly, but it does have a need for what I am trying to do.

I have a customer that uses Epicor for their ERP system. All of the data for the ERP in on an SQL Server and all of the tables in the ERP database are prefixed with "[erp]". So when I reference one of the table in c# the sql would look like this

SELECT * FROM [erp].[tableName]

However if I were to create a new table on the server (in a different database) the table uses the "[dbo]" prefix.

What is the difference with the [erp] and [dbo] prefixes and why does SQL server do this?

I have been tasked with creating a relatively large app that will pull data from 2 or 3 ERP tables and I will need to create a number of new tables in a different database on the same server. When I do work for this customer, I normally would use their VPN to RDP into a development machine on their network which allows direct access to their database. Because of the size of this project, we I've assigned a second developer to work on it. To allow parallel work on the app, I want to recreate 2 or 3 of the customers' ERP tables on our local SQL Server to do a bulk of the development locally, but when I create the tables locally, they have a [dbo] prefix instead of the [erp] prefix.

Any insight would be appreciated.
Thank
Kevin

[jdelano]

Have a look at this
https://learn.microsoft.com/en-us/sq...l-server-ver16
https://www.sqlservertutorial.net/sq...create-schema/

[wqweto]

[erp] and [dbo] are different schemas. Think of this as namespace in .Net, a logical separation of db object that object names accross different schemas do not collide. Another possible use of db schemas is permission assignment -- a user can have read/write permissions on [erp] schema but read-only on [dbo] or no access at all.

When creating and accessing objects in your db (tables, views, sps, etc.) you can prefix object name with optional schema name like CREATE TABLE erp.MyTable or CREATE PROC dbo.usp_MyProc

When schema is omitted from object names query planner uses default schema for the current db user which is dbo for sa login (server administrators) and this default resolution might fail for non-admins and/or lead to permission problems.

cheers,
</wqw>

[kebo]

[erp] and [dbo] are different schemas....
cheers,
</wqw>

Thank you for that.
For others who may stumble on this, if the schema does not exist, you cannot create a table in it. You can create schemas using this,

CREATE SCHEMA schemaName

kevin