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

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
LVL 1
rgb192Asked:
Who is Participating?
 
aplusexpertCommented:
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
 
davemj9876Commented:
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
 
Loganathan NatarajanLAMP DeveloperCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Loganathan NatarajanLAMP DeveloperCommented:
0
 
rgb192Author Commented:
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
 
Loganathan NatarajanLAMP DeveloperCommented:
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
 
rgb192Author Commented:
thanks for db script
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.