SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to delete a datas in whole database?


How to delete a datas in whole database?

Author
Message
tkrajasekar
tkrajasekar
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 46
Hi,
I am Rajasekar,

How to delete a records in whole database(in this database having more than 200 tables)
Ross McMicken
Ross McMicken
Mr or Mrs. 500
Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)Mr or Mrs. 500 (546 reputation)

Group: General Forum Members
Points: 546 Visits: 2232
Use the information_schema.tables view to help you write a script that does what you need. I do this regularly with selects similar to select 'first part of text ' + table_name + ' rest of text' from information_schema.tables where criteria for table names.

Run the script to text and then copy the output to a query window and execute. We have computers, let htem do the hard, repetitive work.
blurpyblurp
blurpyblurp
SSC-Enthusiastic
SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)SSC-Enthusiastic (164 reputation)

Group: General Forum Members
Points: 164 Visits: 197
Since you want to do the "whole" db. Would it not be faster to just script all the tables and associated constraints & settings (or probably the whole DB), drop the old tables create new ones?

I guess the potential pitfall is any internals (like hypotheticals) for performance are potentially lost but it could also be an improvement?
Steve-3_5_7_9
Steve-3_5_7_9
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1438 Visits: 1599
This database doesn't have any "lookup" tables? It's all user data?

Either above suggestion would work, as long as you don't have "lookup" table data, then you'll need to customize either of the two suggestions.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search