.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Sql Scripts - Delete all Tables, Procedures, Views and Functions

Posted By: Rob Chartier     Posted Date: August 21, 2010    Points: 0   Category :ASP.Net

In a shared environment you typically don't have access to delete your database, and recreate it for fresh installs of your product. 

I managed to find these scripts which should help you clean out your database.

Use at your own risk.


Delete All Tables

--Delete All Keys


View Complete Post

More Related Resource Links

How to take the scripts of the changed SPs/Views/Temp tables/Functions automatically?



  We have a huge Database with more than 100 tables , more than 150 SPs.We are 10 members and we will be creating/deleting/updating SPs , functions, views, tem tables  everyday. Actually at the end of the day, we have to send the  scripts of latest SPs,functions,views,Temp tables to the server. So far what we are doing is  we manually go to the SQL server Scripts Wizard and selecting one by one all the SPs,functions,TTables and views. Is there any quicker way to take the changed stuffs instead going manually?

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common ta


Any ideas how I can order inside the subquery? I get the following error. I have tried ORDER BY MAX([Count]) but it doesn't like this much either... Also, I don't want to use TOP in the subquery as I need to use all the records!

			 COUNT(*) AS [Count]
		GROUP BY System_User_ID
		ORDER BY [Count]) l
	INNER JOIN aspnet_Membership m
	ON CAST(m.UserId AS VARCHAR(36)) = l.System_User_ID
Msg 1033, Level 15, State 1, Line 11
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

MS Access and Stored Procedures or Views

Can Microsoft Access access a stored procedure on the SQL Server? If not, is there any way to assign a parameter to a view? I can link to a view in Access, but I cannot link to a procedure. I need to SELECT * from aTable WHERE ID = [@PassidInID]. Any way to do this through Access? The reason for this, is because I am trying to run an existing query (which is very busy - using other queries with queries in them etc.), and I keep getting an ODBC error. I am thinking that if I move the queries to the SQL Server, it may get rid of this error? It's a 2-tier app I have. Tables on the server, forms, queries, and everything else in Access. Thanks in advance - I hope.

Using SQL Views like Tables from DB

Unfortunatrly I've not been able to google what I'm looking for, so either its not a problem for others or there is to much other "noise" so what 'm looking for doesn't show up.Using .net 4, MVC 3 I can pull data from a table in SQL Server as follows:        public ActionResult IndexTelDataset()        {            // Table: Customer             CustomersTableAdapter ta = new CustomersTableAdapter();            NorthwindDataset.CustomersDataTable Customers = new NorthwindDataset.CustomersDataTable();            ta.Fill(Customers);             // Return view              return View(Customers);        }How would I do the same using a vew called vwCustomerDetails? Also on the same topic whats the best way to do a query such as "Select * from Customers".  Thanks!Quentin

Delete from multiple tables

 After trying myriad ways to accomplish deleting from two table, I found a way that actually works. However, AM am sure there is a better way to do this. Any ideas?  Imports System.Data Imports System.Data.SqlClient Imports Telerik.Web.UI Imports System.CodeDom Imports System.Web Partial Class Default2 Inherits System.Web.UI.Page Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim sql As String Dim strConnString As [String] = System.Configuration.ConfigurationManager.ConnectionStrings("CF_SQL_Connection").ConnectionString() sql = "DELETE FROM Table1 WHERE IDTable1 = '5'" Dim connection As New SqlConnection(strConnString) Dim command As New SqlCommand(sql, connection) command.Connection.Open() command.ExecuteNonQuery() command.Connection.Close() Dim sql1 As String Dim strConnString1 As [String] = System.Configuration.ConfigurationManager.ConnectionStrings("CF_SQL_Connection").ConnectionString() sql1 = "DELETE FROM Table2 WHERE IDTable1 = '5'" Dim connection1 As New SqlConnection(strConnString) Dim command1 As New SqlCommand(sql1, connection) command1.Connection.Open() command1.ExecuteNonQuery() command1.Connec

linq - delete two tables not work

BurnbetDataContext db = new BurnbetDataContext(); var q = (from c in db.GetTable<tbl_Payment>() where c.UserId == "fc44c835-8e80-45ee-af84-68339a09c053" select c).Single<tbl_Payment>(); db.GetTable<tbl_Payment>().DeleteOnSubmit(q); db.SubmitChanges(); In App_Code I BurnbetDataContext.I have two tables and tbl_PaymentDetails tbl_PaymentAssociation is:Parent Child Class -->>> UserId UserId ClassData in Table tbl_PaymentDetails (child) does not delete.      

Enterprise t-sql Stored Procs against tables -vs- Views

I work for a very large enterprise and I am wanting to know what is the best way to design t-sql for the stored proces to be used from an application? Option 1 - Be sure to optimize your Database design so your application will only require at most 6 joins and used stored procs for queries using proper indexs to gain the most performance Option 2 - Create Views and use stored procs against the views My questions:  1. What is the perfromance hit in using views.   My thought process is that views should only be used when you know the data you want to return "hence the name 'VIEW', should not be used as a query".   2. Are stored procs against the schema tables the best for performance. 3. What is the best way to tune and ensure your stored proc is optimal with the correct indexes, so you build a lighting fast query? http://www.mmwebs.com

dataset - delete tables - Cascade

I have two table:- tbl_Payments- tbl_PaymentDetailsMy code:DataSet1TableAdapters.tbl_PaymentsTableAdapter cta = new DataSet1TableAdapters.tbl_PaymentsTableAdapter();cta.DeleteQuery();Dataset query:DELETE FROM tbl_PaymentsFROM            tbl_Payments INNER JOIN                         tbl_PaymentDetails ON tbl_Payments.ID = tbl_PaymentDetails.PaymentIdRelation:Key columns (tbl_Payments) | Foreign Key Columns (tbl_PaymentDetails)ID                                          | PaymentIdChoose what to createSelect: Foreign Key Constraint OnlyUpdate Rule: CascadeDelete Rule: CascadeWhy delete only the data from the first table?Data from other tables remain.Please help.LangDetecten>sl GoogleDicCCascade

Delete records if it is not getting inserted and also in the related tables

Hi All, If the insert into table person is successful, but the insert into table subject fails, delete the person record and all records associated with the person that were also inserted. If the insert into table person is successful, and the insert into subject is successful, but the insert into the booking table fails, delete the subject record, the person record ,and all records associated with the person that were also inserted. The reason for the failure into the 3 tables (person, subject, booking) should be logged to tblErrors . Also, a message should be written to tblErrors which logs the fact that this situation has occurred.     -- I have few stored procs which does inserts and updates How would i capture the record which is not getting inserted and log in to log table and delete the previous records which got inserted  which are related to that.

Does a Maintenance Plan "Rebuild Index" task on Tables + Views rebuild the full text indexes ?


As subject really.

I'm talking about a complete rebuild - not an incremental, or a re-organise.

SQL 2005

The full text indexes are only on individual tables, not Views.

I've asked a similar question before, but sufficiently different, and other is marked as Answered so thought I'd open a new Q : http://social.msdn.microsoft.com/Forums/en/sqltools/thread/5f17f4f1-fba9-436f-aedf-cfbfd89c8db2)


how to delete records from multiple tables.


Hi Friends,Tell me How to delete records from multiple tables by using a single query.

avoid accidental delete on Subscriber tables - transactional replication


Hello pundits..

I am looking to see if we have any way to set on the subcsriber to avoid accidental deletes on subscriber tables to avoid the 20598 error(where the publisher is trying to replicate a record not existing(rather deleted in accident) at the subscriber end). This is for transactional replication


Stored Procedures using XQuery to insert XML data into tables


If I have the following XML being passed as a parameter into a stored procedure.


<name>My Bookstore</name><br/>
<location>New York</location><br/>
 <title lang="eng"&g

How do I execute SQL2008 functions or procedures within VB Applics?


I am able to create "functions"  in SQL2008, but I cannot understand how to execute them within my Visual Studio applications. I have many books that I've purchased which help me greatly, but I find none that discuss directly how to use SQL with objects on a windows form or how to create actions using "Functions" or "Procedures" by connecting them to objects like bttns or combo boxes to execute them.  There needs to be more help in directions that show how to tie screen applicatons to server side applications.  I am able to use Queries within datasets,but not functions or procedures.


Problem I am trying to solve:

I have a Login form that uses two parameters, @UserName and @UserPassword to select a row from my UserTable and to get the data for column "UserStatus" to verify that content shows "Active" rather than "Inactive" or "Null".  When all conditions are correct the "LogIn" Bttn allows movement to the opening screen which is a loan list where a loan (i.e.  "LoanNo") is chosen.  When a loan number is selected, all other screens and datasets should be filtered to that single loan selection and locked to that user until released.

I  own SQL 2008 and Visual Studio2008, but have  2010

Find all stored procedures/functions granted to a specific user


SQL 2005.

How can I find all routines (sp, fn) exec granted to a specific user in a given database ?

Thanks !!

Can't SSRS 2008 handle stored procedures which return multiple tables?


I tried it and it only returns the first table.

Any suggestion?

Thank you,


SQLCE 4.0 "Update ... From" "Views" "Stored Procedures"


Is there documentation on 4.0? 

Are the following implemented:

Update ... From


Stored Procedures

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend