SQL Server Performance

table name as parameter

Discussion in 'General Developer Questions' started by v1rt, Aug 18, 2008.

  1. v1rt New Member

    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
  2. preethi Member

    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.
  3. v1rt New Member

    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
  4. v1rt New Member

    Disregard my last post, I found a code. :)
  5. Madhivanan Moderator

  6. Madhivanan Moderator

    [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

Share This Page