2

I have a database installed on four different production servers. Each server has its own different user. When I create a new table using create statement on these servers every server sets / prepends their user name e.g.

Create Table tab_1 ... will be created as
abc.tab_1 on server_1 using abc as login
def.tab_1 on server_2 using def as login
ghi.tab_1 on server_3 using ghi as login
jkl.tab_1 on server_4 using jkl as login

I want to use dbo as default schema and have to use following statement on every server for every new table

ALTER SCHEMA dbo TRANSFER login.table_name;

Is there any way to modify Create Table statement or other setting by which every table is created on all servers as dbo.table_name

I am using shared hosting and there are many restrictions in SSMS, so a query based solution would be appropriate.

haraman
  • 2,744
  • 2
  • 27
  • 50
  • http://stackoverflow.com/questions/17571233/how-to-change-schema-of-all-tables-views-and-stored-procedures-in-mssql – A_Sk Nov 03 '15 at 18:19
  • @عجمان This link describes changing the schema after table is created and I have already mentioned it above. I am looking for setting the default schema for all new tables so that I do not have to change it later. Answer given by janschweizer is promising but I wonder can it be set even without specifying in the Create statement? – haraman Nov 03 '15 at 18:47

1 Answers1

5

You have control over the create table queries? In that case simply specify dbo in create:

CREATE TABLE dbo.dummy ();

For changing default schema for a user see this answer: https://stackoverflow.com/a/8208124/3480246

Community
  • 1
  • 1
Jan
  • 13,738
  • 3
  • 30
  • 55
  • That's a good option. Even though, can it be set permanently or by default so that all new tables are automatically created under dbo schema without explicitly mentioning. – haraman Nov 03 '15 at 18:51
  • See my edit - you could set default schema for the users. – Jan Nov 03 '15 at 19:35