| Author |
Topic |
|
softalan
Starting Member
1 Post |
 Posted - 2007-04-11 : 18:05:42
|
| 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
38200 Posts |
Posted - 2007-04-11 : 18:12:30
|
| Run this to generate the code:SELECT 'TRUNCATE TABLE ' + TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME LIKE 'RESULTS%'Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 2007-04-11 : 21:33:11
|
| Nice code Tara!You can also do it with a cursor loop and dynamic SQL.Kalman Toth, Database, DW & BI ArchitectSQL Server 2005 Training - http://www.sqlusa.com |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-11 : 21:42:55
|
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 Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
SQLUSA
Starting Member
28 Posts |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-04-11 : 22:59:28
|
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 |
|
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 2007-04-12 : 06:00:40
|
| That depends on your architecture and firewall settings!Microsoft says stored procs are your best protections against crooks.Kalman Toth, Database, DW & BI ArchitectSQL Server 2005 Training - http://www.sqlusa.com |
|
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 2007-04-12 : 06:03:23
|
| Very interesting article! (sommarskog)Thanks for bringing it to my attentionKalman Toth, Database, DW & BI ArchitectSQL Server 2005 Training - http://www.sqlusa.com |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-04-12 : 06:06:59
|
| Best protection against theft is "TRUST NO ONE".And how useful is a firewall against gruntled (inhouse) employees?Peter LarssonHelsingborg, Sweden |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-04-12 : 06:07:48
|
| 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_optimizationMaybe we should ban him?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
|
|
|
pootle_flump
1064 Posts |
Posted - 2007-04-12 : 06:24:48
|
quote:
Originally posted by SQLUSAMicrosoft says stored procs are your best protections against crooks.
lol. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-04-12 : 07:23:14
|
| "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 |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
How Can I Truncate Multiple Tables?