Hi folk, I would like to make a stored procedure for truncating tables. However, I would like to add the table name as one of the parameters. How do I do that? Thanks. V1rt
Warning 1:You are trying something extremely dangerous. Truncating table will distroy all data. There is no way of recovering other than restoring from a backup/database snapshot. It will simply remove the links between table header and data pages. Warning 2: TRUNCATE TABLE will not work if the table is referenced by another table. (even if the other tabel is empty) You may have to think of DELETE without where clause to do the same to tables where referential integrity is maintainied Now, Let me coem tot he answer: Use dynamic SQL. Warning 3:This is another dangerous command. unless you validate the data fully, Others can use SQL injection method and destroy your entire database.
Thank you so much. I'm not worried since it is not going to be used by a web based application. We only have 6 users and the app is a stand alone. So if dynamic SQL, can you please share to me how the sql code would be? And yes, I am aware of what TRUNCATE data does. Thanks, V1rt
[quote user="v1rt"] Disregard my last post, I found a code. [/quote] Make sure you read this www.sommaarskog.se/dynamic_sql.html
[quote user="preethi"] Warning 1:You are trying something extremely dangerous. Truncating table will distroy all data. There is no way of recovering other than restoring from a backup/database snapshot. It will simply remove the links between table header and data pages. Warning 2: TRUNCATE TABLE will not work if the table is referenced by another table. (even if the other tabel is empty) You may have to think of DELETE without where clause to do the same to tables where referential integrity is maintainied Now, Let me coem tot he answer: Use dynamic SQL. Warning 3:This is another dangerous command. unless you validate the data fully, Others can use SQL injection method and destroy your entire database. [/quote] 1 Truncate can be rollbacked within a single scope
