| Author |
 Topic  |
|
|
softalan
Starting Member
1 Posts |
 Posted - 04/11/2007 : 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
USA
35007 Posts |
Posted - 04/11/2007 : 18:12:30
|
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/ |
 |
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 04/11/2007 : 21:33:11
|
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 |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 04/11/2007 : 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 Kizer
http://weblogs.sqlteam.com/tarad/ |
|
|
|
SQLUSA
Starting Member
28 Posts |
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2871 Posts |
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 04/12/2007 : 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 Architect
SQL Server 2005 Training - http://www.sqlusa.com |
|
|
|
SQLUSA
Starting Member
28 Posts |
Posted - 04/12/2007 : 06:03:23
|
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 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 04/12/2007 : 06:06:59
|
Best protection against theft is "TRUST NO ONE".
And how useful is a firewall against gruntled (inhouse) employees?
Peter Larsson
Helsingborg, Sweden |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
pootle_flump
Flowing Fount of Yak Knowledge
United Kingdom
1064 Posts |
Posted - 04/12/2007 : 06:24:48
|
quote:
Originally posted by SQLUSAMicrosoft says stored procs are your best protections against crooks.
lol. |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 04/12/2007 : 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
India
22461 Posts |
|
| |
Topic |
|
How Can I Truncate Multiple Tables?
