Solved

dropping database tables without dropping database or finding all the table names

Posted on 2013-01-21
7
290 Views
Last Modified: 2013-01-23
delete all the tables from a database
many tables, do not want to copy and paste many times





I can not just drop the database and create database
because I am using shared hosting
0
Comment
Question by:rgb192
7 Comments
 
LVL 2

Expert Comment

by:davemj9876
ID: 38803969
What level of access do you have to the database.  Command line, only through something like php etc?  The answer will depend on what access you have
0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 38804063
I think you need like this simple mysql management tool (not like phpMyAdmin) .. download this ,  configure and upload in your shared hosting,
http://sqlbuddy.com/

so you can easily access Db tables, change, alter all are possible.
0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 38804065
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:rgb192
ID: 38804155
i am using mysql workbench query tool

because I have shared hosting, I can not create a database
I can do many operations (delete database, create/delete table)
0
 
LVL 36

Expert Comment

by:Loganathan Natarajan
ID: 38804163
If you have using shared hosting, it might be limited to create db. just ask them for more details.

also, you can get phpMyAdmin access on browser itself.
0
 
LVL 7

Accepted Solution

by:
aplusexpert earned 500 total points
ID: 38805082
Try this script. this may help you

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR

SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1

LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)

BEGIN

Exec SP_EXECUTESQL @Sql

FETCH NEXT FROM @Cursor INTO @Sql

END

CLOSE @Cursor DEALLOCATE @Cursor

GO

EXEC sp_MSForEachTable 'DROP TABLE ?'

GO

Open in new window


OR

EXEC sp_msforeachtable 'DROP TABLE ?'

Open in new window


Thanks
0
 

Author Closing Comment

by:rgb192
ID: 38813087
thanks for db script
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Multilanguage Database Design in MySQL 5 106
Trigger usage 2 67
Amazon Redshift 2 33
MySqlDump not dumping triggers 1 30
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

805 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question