SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How Can I Truncate Multiple Tables?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

softalan
Starting Member

1 Posts

Posted - 04/11/2007 :  18:05:42  Show Profile  Reply with Quote
Hi,

I'm quite new to SQL 2000 and I've been trying to find an easy way to truncate a number of tables in a script. The table names are all prefixed with 'RESULTS_' so they are easy enough to identify. Is this fairly straightforward?

tkizer
Almighty SQL Goddess

USA
37143 Posts

Posted - 04/11/2007 :  18:12:30  Show Profile  Visit tkizer's Homepage  Reply with Quote
Run this to generate the code:

SELECT 'TRUNCATE TABLE ' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'RESULTS%'

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 04/11/2007 :  21:33:11  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
Nice code Tara!

You can also do it with a cursor loop and dynamic SQL.



Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37143 Posts

Posted - 04/11/2007 :  21:42:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:
Originally posted by SQLUSA



You can also do it with a cursor loop and dynamic SQL.



This is the New to SQL Server forum. Answers should provide more details than usual. We can't make the assumption that the poster even knows what dynamic SQL is. Please be much more descriptive in your answers in this forum.

Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 04/11/2007 :  22:05:27  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
Thanks Tara for the helpful hints!

Here is an example for dynamic SQL : http://www.sqlusa.com/bestpractices/dynamicsql/.

And this is an example for a cursor: http://www.sqlusa.com/bestpractices/sequencewithcursor/.

NEWBIE: Please change over Tara's script into a cursor loop with dynamic sql exacution and list the script for us.

Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 04/11/2007 :  22:59:28  Show Profile  Visit jezemine's Homepage  Reply with Quote
quote:
Originally posted by SQLUSA

Here is an example for dynamic SQL : http://www.sqlusa.com/bestpractices/dynamicsql/



procs such as this one are a great way to give your data away to criminals.

You might want to think about moving this example from the "bestpractices" folder to the "lastresorts" folder. or "whatnottodo".

better practices for dynamic sql can be found here: http://www.sommarskog.se/dynamic_sql.html




www.elsasoft.org
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 04/12/2007 :  06:00:40  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
That depends on your architecture and firewall settings!

Microsoft says stored procs are your best protections against crooks.

Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

SQLUSA
Starting Member

28 Posts

Posted - 04/12/2007 :  06:03:23  Show Profile  Visit SQLUSA's Homepage  Reply with Quote
Very interesting article! (sommarskog)

Thanks for bringing it to my attention

Kalman Toth, Database, DW & BI Architect
SQL Server 2005 Training - http://www.sqlusa.com
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30265 Posts

Posted - 04/12/2007 :  06:06:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Best protection against theft is "TRUST NO ONE".
And how useful is a firewall against gruntled (inhouse) employees?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11751 Posts

Posted - 04/12/2007 :  06:07:48  Show Profile  Visit spirit1's Homepage  Reply with Quote
have you completly lost your mind?

yes sprocs are the best protection but not in this way.
as an instructor you should know about SQL Injection.

This whole SQLUsa thing looks like a SEO optimization technique.
http://en.wikipedia.org/wiki/Search_engine_optimization

Maybe we should ban him?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
Go to Top of Page

pootle_flump
Flowing Fount of Yak Knowledge

United Kingdom
1064 Posts

Posted - 04/12/2007 :  06:24:48  Show Profile  Reply with Quote
quote:
Originally posted by SQLUSAMicrosoft says stored procs are your best protections against crooks.

lol.
Go to Top of Page

Kristen
Test

United Kingdom
22415 Posts

Posted - 04/12/2007 :  07:23:14  Show Profile  Reply with Quote
"That depends on your architecture and firewall settings!"

No, they stop attacks on the server directly. They do not prevent an attack through the front end application itself, e.g. a web site, which inherently has a connection which is "trusted" by the SQL server.

"Microsoft says stored procs are your best protections against crooks."

No. What you are correctly implying, but have described wrongly, is that Sprocs generally do not use dynamic SQL, and thus resist attacks better than dynamic SQL. But Dynamic SQL can easily be made bullet-proof, and Sprocs can use dynamic SQL - and when they do the way they are written is a) often very poor with regard to preventing attacks and b) VERY much out-of-sight-out-of-mind and therefore an increased risk.

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 04/12/2007 :  07:29:16  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Also refer this
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000