Need to empty my database?

Question

Need to empty my database?

Viewing 15 posts - 16 through 30 (of 32 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Sarab. (3/5/2010)
Yes you are right, this approach will surely help you
for fk keys
i think the below mentioned query may help in case of fk relationship
Exec sp_MSforeachtable 'alter table ? nocheck constraint all';

No offense but thinking doesn't get anybody very far... again why don't you test to make sure it works??

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

The download will include all of the tools available in the toolkit. There are two tools that you would be interested in for this project:

SQL Compare

SQL Data Compare

The first one performs a schema comparison, the second will actually compare and synchronize the data in the tables. You could use the second one to copy over lookup and/or setup data for the system.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Lowell SSC Guru Points: 323518 More actions · Answer 3

in my experience, deleting all rows in all tables is not the right thing to do; i'm sure there are many tables that are "lookup' tables used to populate drop downs like "Open / Closed" statuses, list of all states, etc that are necessary for the apps to connect to.

deleting those tables that the app expects to use as drop downs either breaks the app in some cases, or makes data entry impossiblle (STATE require, but the Drop down has no values to select. for example.)

i've always had to pick the specific tables that are data related vs lookup/application required and determine which to trim down.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sarab_SQLChamp Hall of Fame Points: 3882 More actions · Answer 4

Ninja's_RGR'us (3/5/2010)
Sarab. (3/5/2010)
Yes you are right, this approach will surely help you
for fk keys
i think the below mentioned query may help in case of fk relationship
Exec sp_MSforeachtable 'alter table ? nocheck constraint all';
No offense but thinking doesn't get anybody very far... again why don't you test to make sure it works??

yes, you are right.

my mistake.:unsure:

Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek

Angela S. Old Hand Points: 328 More actions · Answer 5

The download will include all of the tools available in the toolkit. There are two tools that you would be interested in for this project:
SQL Compare
SQL Data Compare
The first one performs a schema comparison, the second will actually compare and synchronize the data in the tables. You could use the second one to copy over lookup and/or setup data for the system.
Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs

I'm not quite sure I understand how I'm supposed to get the database copied over. I've downloaded, SQL Compare and SQL Data Compare at this point, but am unsure how to use it properly. I'll look up some help from the program but if you could give me some more pointers that would be fantastic. I appreciate all of your assistance. Thank You So much

Angi

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

I've used it only once but as far as I remember you basically select both environements. Then you run a compare between a and b.

Once done a sql script is generated to upgrade a to b.

Then logon to server b and run the script to upgrade it to its new version.

How course this skips the source control and deployement procedures I'm sure you have in place but that's the gist of it.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

That is in essence how the RedGate tools work. The tools will generate a script, you would need to click a button to show the script so you could copy it. The tools also offer the ability to run the scripts directly from the tools. IN many cases, it is better to copy the script and run it from SSMS (it will run faster and will be easier to troubleshoot should something arise).

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 8

The two previous posts answered the question - if you need additional help, let us know.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

john.campbell-1020429 Ten Centuries Points: 1194 More actions · Answer 9

Angi,

Yes truncate the tables. Use with sp_msforeachtable to make a one liner something like

sp_msforeachtable 'truncate table ?'

This may need some tweaking, sorry I don't have a db to play with at the moment, but that should get your job done and can be put into a 1 liner job step.

John.

Steve Jones - SSC Editor SSC Guru Points: 736183 More actions · Answer 10

ms_foreachtable might not work if you have FK relationships. I don't believe the RI will allow you to truncate the tables.

Lowell SSC Guru Points: 323518 More actions · Answer 11

here's a script that generates the DELETE FROM / TRUNCATE TABLE statements in foreign key hierarchy order; like i said before, i've found it's rare you want to delete all tables, becaus ethere is usually a lot of lookup tables that should not be trimmed.

nocount on

declare @level tinyint

set @level = 0

create table #tables (

id int not null primary key clustered,

TableName varchar(255) not null,

Level tinyint not null)

insert into #tables (id, TableName, Level)

select id, '[' + user_name(uid) + '].[' + rtrim(name) + ']' as TableName, 0

from sysobjects where xtype = 'U' and status > 0

while @@rowcount > 0 begin

set @level = @level + 1

update rt set Level = @level

from #tables rt

inner join sysreferences fk on fk.rkeyid = rt.id

inner join #tables ft on ft.id = fk.fkeyid

where ft.Level = @level - 1

end

print 'USE ' + DB_NAME() + '

'

select 'TRUNCATE TABLE ' + TableName from #tables where level = 0

select 'DELETE ' + TableName from #tables where level > 0 order by level

drop table #tables

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Angela S. Old Hand Points: 328 More actions · Answer 12

I don't want to delete ANY tables, I just want to create a copy of the database and remove the data. I need the tables, intact just like the main database. This database will be used to complete validation testing so we are messing around with the "REAL" database.

Lowell SSC Guru Points: 323518 More actions · Answer 13

Angela S. (3/9/2010)
I don't want to delete ANY tables, I just want to create a copy of the database and remove the data. I need the tables, intact just like the main database. This database will be used to complete validation testing so we are messing around with the "REAL" database.

so what you want to do is take a backup of the live database, and then restore it as a new database.

then on that test /copy database, run the script I posted.

That would remove all the data, but leave in place all the tables/procs/views/functions/etc.

then you can use that database for validation testing.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Angela S. Old Hand Points: 328 More actions · Answer 14

OH ok Gotcha...will try that now...let you know how things go... BTW thank YOU VERY much for writing the script to help me. I really appreciate that you have taken the time to help me. Thanks again!

Angi

Angela S. Old Hand Points: 328 More actions · Answer 15

OK Now I"m going brain dead. I had a copy of my database...named it LIMSTRAIN, but ended up deleting cause was gonna try the options in this list. NOW i don't remember how I got the copy of the database to begin with. Sorry guys...I"m not having a good week here I'm going crazy!!!! :crazy: