{"id":381,"date":"2013-11-20T16:04:54","date_gmt":"2013-11-20T22:04:54","guid":{"rendered":"http:\/\/www.sqlfreelancer.com\/blog\/?p=381"},"modified":"2014-03-07T16:08:40","modified_gmt":"2014-03-07T22:08:40","slug":"change-schema-on-all-sql-tables","status":"publish","type":"post","link":"https:\/\/www.sqlfreelancer.com\/blog\/change-schema-on-all-sql-tables\/","title":{"rendered":"Change Schema on all SQL tables"},"content":{"rendered":"<p>Today I ran into a problem where all of my SQL Server tables used a different schema than dbo and the application couldn\u2019t understand the different schema. Using the ALTER SCHEMA statement you can chance the schema of a table, for example, the following statement will change the schema from <i>compmsauser <\/i>to <i>dbo<\/i>.<\/p>\n<div>\n<pre>ALTER SCHEMA dbo TRANSFER compmsauser.tablename<\/pre>\n<\/div>\n<p>This works perfectly unless you have to change hundreds of table schemas. The following query will create the T-SQL needed to change every table: (change the WHERE clause to the schema you need to replace)<\/p>\n<div>\n<pre>SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o.Name\r\nFROM sys.Objects o\r\nINNER JOIN sys.Schemas s on o.schema_id = s.schema_id\r\nWHERE s.Name = 'compmsauser'\r\nAnd (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')<\/pre>\n<\/div>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Schema.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-382\" alt=\"SQL Freelancer SQL Server Schema Changes\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Schema.png\" width=\"545\" height=\"305\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Schema.png 545w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Schema-300x167.png 300w\" sizes=\"auto, (max-width: 545px) 100vw, 545px\" \/><\/a><br \/>\nThis query will create the ALTER SCHEMA statement for you! All you have to do now is copy and paste all of the results in a new query window and execute.<\/p>\n<p><a href=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Schema-1.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-383\" alt=\"SQL Freelancer SQL Server Schema Changes\" src=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Schema-1.png\" width=\"581\" height=\"252\" srcset=\"https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Schema-1.png 581w, https:\/\/www.sqlfreelancer.com\/blog\/wp-content\/uploads\/2014\/03\/SQL-Server-Schema-1-300x130.png 300w\" sizes=\"auto, (max-width: 581px) 100vw, 581px\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Today I ran into a problem where all of my SQL Server tables used a different schema than dbo and the application couldn\u2019t understand the different schema. Using the ALTER SCHEMA statement you can chance the schema of a table, for example, the following statement will change the schema from compmsauser to dbo. ALTER SCHEMA [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[53],"tags":[54,86],"class_list":["post-381","post","type-post","status-publish","format-standard","hentry","category-database-design","tag-design","tag-schema"],"_links":{"self":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/381","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/comments?post=381"}],"version-history":[{"count":1,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/381\/revisions"}],"predecessor-version":[{"id":384,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/posts\/381\/revisions\/384"}],"wp:attachment":[{"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/media?parent=381"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/categories?post=381"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlfreelancer.com\/blog\/wp-json\/wp\/v2\/tags?post=381"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}