4

I am creating a smaller sized database in Microsoft SQL Server 2012 to keep run data from machines. The company has production machines and R & D machines. I would like to use the same table for production and R&D with a Type field specifying what the run was for simplicity. I have two schemas (prod and r_d). The permissions for the production and r_d schemas will be different. Is it possible to create a table that belongs to more than one schema? I know you can have the same table name in multiple schemas, but this creates separate objects. I would like to have the one table object to belong to multiple schemas.

Example: CREATE TABLE db_name.prod.r_d.table_name

user3711685
  • 71
  • 2
  • 4
  • As far as I know this is not possible. Can you create a single table in another schema (e.g., dbo) and then create views in each of your schemas with a filter for the correct Type? – AHiggins Jun 05 '14 at 15:04

3 Answers3

9

Consider creating a synonym in one the of schemas, referencing the other schema table:

CREATE SYNONYM r_d.table_name FOR prod.table_name;
Dan Guzman
  • 43,250
  • 3
  • 46
  • 71
2

No, but you can create a view in each schema on to a single table that filters the rows

podiluska
  • 50,950
  • 7
  • 98
  • 104
0

When you said Development and Production. You should consider using separate database and as we go separate server !

For sampling the data you could use a backup of the production database. If you don't want the dev team to have access to production data (avoid data leak), they have to generate their sample data themselves.

Using Synonym or View in your case looks like a bad and dangerous practice !

Marco Guignard
  • 613
  • 3
  • 9