{"id":458,"date":"2013-08-02T07:16:11","date_gmt":"2013-08-02T07:16:11","guid":{"rendered":"http:\/\/ntsplhosting.com\/blog\/\/?p=458"},"modified":"2024-09-18T07:42:46","modified_gmt":"2024-09-18T07:42:46","slug":"sql-server-tips","status":"publish","type":"post","link":"https:\/\/www.ntsplhosting.com\/blog\/sql-server-tips\/","title":{"rendered":"SQL Server Tips"},"content":{"rendered":"<h3>REMOVE ALL TABLES<\/h3>\n<p><code>EXEC sp_MSforeachtable @command1 = \"DROP TABLE ?\"<\/code><\/p>\n<h3>REMOVE ALL USER-DEFINED STORED PROCEDURES<\/h3>\n<div><\/div>\n<div>\n<div><\/div>\n<pre class=\"code-structure\"><code>Declare @procName varchar(500)\r\nDeclare cur Cursor For Select [name] From sys.objects where type = 'p'\r\nOpen cur\r\nFetch Next From cur Into @procName\r\nWhile @@fetch_status = 0\r\nBegin\r\n\u00a0Exec('drop procedure ' + @procName)\r\n\u00a0Fetch Next From cur Into @procName\r\nEnd\r\nClose cur\r\nDeallocate cur<\/code><\/pre>\n<\/div>\n<div><\/div>\n<div>\n<h3>REMOVE ALL VIEWS<\/h3>\n<\/div>\n<div><\/div>\n<div>\n<pre class=\"code-structure\"><code>Declare @viewName varchar(500)\r\nDeclare cur Cursor For Select [name] From sys.objects where type = 'v'\r\nOpen cur\r\nFetch Next From cur Into @viewName\r\nWhile @@fetch_status = 0\r\nBegin\r\n\u00a0Exec('drop view ' + @viewName)\r\n\u00a0Fetch Next From cur Into @viewName\r\nEnd\r\nClose cur\r\nDeallocate cur<\/code><\/pre>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>REMOVE ALL TABLES EXEC sp_MSforeachtable @command1 = &#8220;DROP TABLE ?&#8221; REMOVE ALL USER-DEFINED STORED PROCEDURES Declare @procName varchar(500) Declare cur Cursor For Select [name] From sys.objects where type = &#8216;p&#8217; Open cur Fetch Next From cur Into @procName While @@fetch_status = 0 Begin \u00a0Exec(&#8216;drop procedure &#8216; + @procName) \u00a0Fetch Next From cur Into @procName End [&hellip;]<\/p>\n","protected":false},"author":18,"featured_media":1498,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[30],"tags":[330],"_links":{"self":[{"href":"https:\/\/www.ntsplhosting.com\/blog\/wp-json\/wp\/v2\/posts\/458"}],"collection":[{"href":"https:\/\/www.ntsplhosting.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ntsplhosting.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ntsplhosting.com\/blog\/wp-json\/wp\/v2\/users\/18"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ntsplhosting.com\/blog\/wp-json\/wp\/v2\/comments?post=458"}],"version-history":[{"count":10,"href":"https:\/\/www.ntsplhosting.com\/blog\/wp-json\/wp\/v2\/posts\/458\/revisions"}],"predecessor-version":[{"id":37898,"href":"https:\/\/www.ntsplhosting.com\/blog\/wp-json\/wp\/v2\/posts\/458\/revisions\/37898"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.ntsplhosting.com\/blog\/wp-json\/wp\/v2\/media\/1498"}],"wp:attachment":[{"href":"https:\/\/www.ntsplhosting.com\/blog\/wp-json\/wp\/v2\/media?parent=458"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ntsplhosting.com\/blog\/wp-json\/wp\/v2\/categories?post=458"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ntsplhosting.com\/blog\/wp-json\/wp\/v2\/tags?post=458"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}