{"id":2128,"date":"2018-05-25T22:06:39","date_gmt":"2018-05-26T05:06:39","guid":{"rendered":"https:\/\/database.guide\/?p=2128"},"modified":"2020-02-14T16:30:51","modified_gmt":"2020-02-14T23:30:51","slug":"how-to-remove-a-column-in-sql-server-using-t-sql","status":"publish","type":"post","link":"https:\/\/database.guide\/how-to-remove-a-column-in-sql-server-using-t-sql\/","title":{"rendered":"How to Remove a Column in SQL Server using T-SQL"},"content":{"rendered":"<p>In <a href=\"https:\/\/database.guide\/what-is-sql-server\/\">SQL Server<\/a>, you can create, modify, and remove database objects using a graphical user interface (like using the GUI tools in <a href=\"https:\/\/database.guide\/what-is-sql-server-management-studio\/\">SQL Server Management Studio<\/a>), or you can use <a href=\"https:\/\/database.guide\/what-is-t-sql\/\">Transact-SQL<\/a> to do the same tasks. In fact, the GUI tools actually use T-SQL behind the scenes to perform these tasks.<\/p>\n<p>When using Transact-SQL, you can remove a column from a table by using the <code>ALTER TABLE<\/code> statement. This statement allows you to change a table&#8217;s definition by specifying exactly what changes you require. In our case, we require the removal of a column.<\/p>\n<p><!--more--><\/p>\n<h2>Example<\/h2>\n<pre>ALTER TABLE Tasks\r\n    DROP COLUMN TaskCode;\r\nGO<\/pre>\n<p>This example removes\/(drops) the column called <code>TaskCode<\/code> from the table called <code>Tasks<\/code>. That&#8217;s all we need to do to remove the column.<\/p>\n<p>In this case we also use <code>GO<\/code>. This isn&#8217;t actually part of T-SQL but it is recognised by some utilities\u00a0to signal the end of a batch of T-SQL statements.<\/p>\n<h2>Constraint Error?<\/h2>\n<p>In some cases you might get a constraint error when you try to drop a column from a table. If this happens, it&#8217;s because the column you&#8217;re trying to remove has a constraint that needs to be removed first.<\/p>\n<p>In this case, simply drop the constraint first, then drop the column.<\/p>\n<p>Here&#8217;s an example of dropping a constraint, then dropping the table:<\/p>\n<pre>-- Drop the constraint\r\nALTER TABLE Tasks\r\n    DROP CONSTRAINT taskcode_unique;\r\nGO\r\n\r\n-- Drop the column\r\nALTER TABLE Tasks\r\n    DROP COLUMN TaskCode;\r\nGO\r\n<\/pre>\n<p>This example drops a constraint called <code>taskcode_unique<\/code>, then it drops the column called <code>TaskCode<\/code>.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In SQL Server, you can create, modify, and remove database objects using a graphical user interface (like using the GUI tools in SQL Server Management Studio), or you can use Transact-SQL to do the same tasks. In fact, the GUI tools actually use T-SQL behind the scenes to perform these tasks. When using Transact-SQL, you &#8230; <a title=\"How to Remove a Column in SQL Server using T-SQL\" class=\"read-more\" href=\"https:\/\/database.guide\/how-to-remove-a-column-in-sql-server-using-t-sql\/\" aria-label=\"Read more about How to Remove a Column in SQL Server using T-SQL\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[7,5],"tags":[10,77,61],"class_list":["post-2128","post","type-post","status-publish","format-standard","hentry","category-sql","category-sql-server","tag-how-to","tag-mssql","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2128","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=2128"}],"version-history":[{"count":3,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2128\/revisions"}],"predecessor-version":[{"id":2131,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/2128\/revisions\/2131"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=2128"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=2128"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=2128"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}