Hello.... I've asked before about deleting the oldest table from a SQL DB. I now understand that's not an easy task. Someone stated that it may be easier to just delete the entire SQL DB and then re-create it. After giving it additional thought, I now agree that would be a simpler and faster solution! Yet, a few key questions still remain unanswered! .... 1) - Is this realistic? 2) - If it is, what SQL commands can I use to delete an entire DB? 3) - What SQL commands are available to re-create it? 4) - Do I need a blank file with the extension of MDF? Thanks for you patience. May you have a blessed day as the Lord wills. JEP_Dude
Why you want to drop the database and then re-create it? It would be nice to truncate data of all tables instead of deleting tables. To drop a database use (in MySQL) DROP DATABASE db_name; Code (markup):
That's a good question. Yet, this SQL DB is for a website that processes stock market sahre prices. I've been working on this website so long that its now like a hobbie. Simply put, ... each SQL table of this DB will contain only the current share price for each stock. The older information is considered out of date and therefore of no value. May you have a blessed day as the Lord wills. -- "Therefore the Jews sought the more to kill him, because he not only had broken the sabbath, but said also that God was his Father, making himself equal with God." (John 5:18) "Then answered Jesus and said unto them, Verily, verily, I say unto you, The Son can do nothing of himself, but what he seeth the Father do: for what things soever he doeth, these also doeth the Son likewise." (John 5:19) JEP_Dude
Then you should go for TRUNCATE but if you have AUTO_INCREMENT and think it might create problems by generating same ids again go for DELETE FROM table_name.
Once you drop the database you'll need to recreate it. create database DATABASE_NAME; Then you'll need to assign some user with permissions to read it: CREATE USER 'YOUR_USER_NAME_HERE'@'localhost' IDENTIFIED BY 'YOUR_PASSWORD_HERE'; GRANT ALL ON YOUR_DATABASE_NAME_HERE.* TO 'YOUR_USER_NAME_HERE'@'localhost';
Hey Guys .... Thanks for giving such an experienced reply! .... Please excuse me. I would like to see if I understand this correctly. I only use a SQL db to contain the current price information for each stock within the present market. Usually that's a lot of tables. (For the NASDAQ that slims down to around ~2,700 to 3,300 tables.) Since DELETE of each separate table would be to slow to erase them all, I've decided to just erase the whole SQL db and then recreate it. Afterwards I would create each separate table as their price data arrives. Can I just use: DROP DATABASE NASDAQ; After this delete is complete, there will be nothing remaining. No Diagrams, Tables, Views, Stored Procedures, Functions, Synonyms, Types, or Assemblies will remain. Only a simple blank file on disk with an MDF extension. Then what command do I use to create a simple SQL db with Diagrams, Tables, Views, Stored Procedures, Functions, Synonyms, Types, and Assemblies? Would this simple line complete that task?: CREATE DATABASE NASDAQ; PS: I would also humble myself to ask for only those commands that work with both SQL 2005 and 2008 versions. (I'd like to use both versions because I'd like to avoid any issue of upgrade oversight or entanglements.) -- "My sheep hear my voice, and I know them, and they follow me:" (John 10:27) "And I give unto them eternal life; and they shall never perish, neither shall any man pluck them out of my hand." (John 10:28) "My Father, which gave them me, is greater than all; and no man is able to pluck them out of my Father's hand." (John 10:29) "I and my Father are one." (John 10:30)