Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>When dealing with deleting data from tables which have foreign key relationships - which is basically the case with any properly designed database - we can disable all the constraints, delete all the data and then re-enable constraints</p> <pre><code>-- disable all constraints EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT all" -- delete data in all tables EXEC sp_MSForEachTable "DELETE FROM ?" -- enable all constraints exec sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" </code></pre> <p>More on disabling constraints and triggers <a href="https://stackoverflow.com/questions/123558/sql-server-2005-t-sql-to-temporarily-disable-a-trigger#123966">here</a></p> <p>if some of the tables have identity columns we may want to reseed them</p> <pre><code>EXEC sp_MSForEachTable "DBCC CHECKIDENT ( '?', RESEED, 0)" </code></pre> <p>Note that the behaviour of RESEED differs between brand new table, and one which had had some data inserted previously from <a href="http://msdn.microsoft.com/en-us/library/aa258817(SQL.80).aspx" rel="noreferrer">BOL</a>:</p> <blockquote> <p><strong>DBCC CHECKIDENT ('table_name', RESEED, newReseedValue)</strong></p> <p>The current identity value is set to the newReseedValue. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use newReseedValue as the identity. Otherwise, the next row inserted will use newReseedValue + 1. If the value of newReseedValue is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.</p> </blockquote> <p>Thanks to <a href="https://stackoverflow.com/users/23566/robert-claypool">Robert</a> for pointing out the fact that disabling constraints does not allow to use truncate, the constraints would have to be dropped, and then recreated</p>
    singulars
    1. This table or related slice is empty.
    1. POHow do you truncate all tables in a database using TSQL?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAlex McMillan
    1. USkristof
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTDownMod
    1. CODisabling constraints will NOT allow truncation of tables referenced by a FOREIGN KEY constraint. The FK constraint has to be dropped. Please reply if I am wrong about this, but I have found no way to avoid dropping them.
      singulars
      1. PO
      1. USRobert Claypool
    2. COThanks Robert you are right. Thank you for pointing this out. I have updated my answer.
      singulars
      1. PO
      1. USkristof
    3. COJust a typo "Table" keyword should not be there in this statement EXEC sp_MSForEachTable "DELETE FROM TABLE ?" .Correct version should be : EXEC sp_MSForEachTable "DELETE FROM ?"
      singulars
      1. PO
      1. USRaghav
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload