0

I am trying to add schema to the table 'myTable' which was missed. I want the table to be under 'dbo.myTable'

EDIT

I am not able to add code but the stack overflow is not allowing.. I want to check if exists from sys.tables and do the alter schema transfer.

enter image description here

gowww
  • 139
  • 1
  • 12
  • Not clear at all. – PM 77-1 May 18 '17 at 17:11
  • Possible duplicate of [CREATE TABLE IF NOT EXISTS equivalent in SQL Server](http://stackoverflow.com/questions/6520999/create-table-if-not-exists-equivalent-in-sql-server) – mrogers May 18 '17 at 17:11
  • 1
    Possible duplicate of [How do I move a table into a schema in T-SQL](http://stackoverflow.com/questions/1149159/how-do-i-move-a-table-into-a-schema-in-t-sql) – NP3 May 18 '17 at 17:11
  • I am not able to add code but the stack overflow is not allowing.. I want to check if exists from sys.tables and do the alter schema transfer. – gowww May 18 '17 at 17:20

2 Answers2

1

A table in SQL Server will always belong to a schema, with default settings it will belong to the dbo schema if no other is specified.

Jeffrey Van Laethem
  • 2,601
  • 1
  • 20
  • 30
1

The default schema is dbo, but if it is in a different schema for some reason you can alter schema to transfer the table.

alter schema dbo transfer [current_schema_name].mytable;

To transfer a schema if a table exists you could use something like this: 

if exists (select 1
           from sys.objects o
           where schema_name(o.schema_id)<>N'dbo'
             and o.name = 'mytable'
           )
alter schema dbo transfer test.mytable;

rextester demo: http://rextester.com/TEBRQL57704

SqlZim
  • 37,248
  • 6
  • 41
  • 59