0

I have here example can truncate table

EXEC sp_MSforeachtable @command1="TRUNCATE TABLE ? NOCHECK CONSTRAINT ALL";

but the error says.

Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'NOCHECK'.

how to create a script which can truncate all the data in the tables in just 1 command, and can force to truncate with FOREIGN KEY

Thanks

DineshDB
  • 5,998
  • 7
  • 33
  • 49
Dev George
  • 49
  • 13
  • Use single quotes not double quotes. – Gordon Linoff Apr 02 '18 at 11:33
  • same error.. even if its single or double – Dev George Apr 02 '18 at 11:33
  • `TRUNCATE TABLE` does *not* have any of those options. Which is what the error says too. Have you tried to truncate a single table with that command? – Panagiotis Kanavos Apr 02 '18 at 11:35
  • i can do truncate in just table. but the question is how can i truncate all the table in the my main database? – Dev George Apr 02 '18 at 11:37
  • No you can't, not with *that* command – Panagiotis Kanavos Apr 02 '18 at 11:37
  • 1
    Might want to check how to do it here: https://stackoverflow.com/a/156813/6492765 . – MK_ Apr 02 '18 at 11:37
  • 1
    Possible duplicate of [How do you truncate all tables in a database using TSQL?](https://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql) – MK_ Apr 02 '18 at 11:37
  • TSQL or SQL is that same? – Dev George Apr 02 '18 at 11:39
  • @MK_ the first link doesn't show *truncation*, the second will fail if there are foreign keys, even disabled – Panagiotis Kanavos Apr 02 '18 at 11:40
  • 2
    @DevGeorge, you can't just create new `TRUNCATE TABLE` syntax to do what you want. One cannot truncate a table referenced by a foreign key. You'll need to drop the referencing FKs, truncate, and recreate the constraints. – Dan Guzman Apr 02 '18 at 11:40
  • im finding new solution for the question... easily fast understand. – Dev George Apr 02 '18 at 11:40
  • @DevGeorge if you don't know that difference *stop right now* before you nuke a production database. Why do you want to delete all data anyway? What is the *actual* problem you want to solve? There are probably less risky ways to do it. – Panagiotis Kanavos Apr 02 '18 at 11:41
  • @PanagiotisKanavos the main purpose is that. after the database. i want to make it empty all the data to that database.. thanks..! – Dev George Apr 02 '18 at 11:42
  • 2
    @DevGeorge for example, if you want to create an empty database for deployment, you can *script it*. Or export a dacpac without any data. Or create a Database project with SSDT, which would also allow you to version control the database – Panagiotis Kanavos Apr 02 '18 at 11:42
  • @DevGeorge no that's not the main purpose. That's the initial statement. The question is *why* do you want to do that? What is the problem to which you thing rimrafing the database is the solution? And why did you try random hints instead of eg, checking the documentation? – Panagiotis Kanavos Apr 02 '18 at 11:42
  • Possible duplicate of https://stackoverflow.com/questions/3843806/disabling-foreign-key-constraint-still-cant-truncate-table-sql-server-2005 – Udai Krishnan Apr 02 '18 at 11:42
  • I'd like to add that you CANNOT truncate with the foreign keys on. You can only delete from the tables if you turn the constraints off for the time being. @PanagiotisKanavos is right, if you want to just get the schema to somewhere youc an just script it without data. What do you really want to achieve? – MK_ Apr 02 '18 at 11:44
  • @PanagiotisKanavos i just follow my boss. he said that after i back up the database. i need to empty the database exist.. so thats why . – Dev George Apr 02 '18 at 11:45
  • any suggestion for that..?? possible to fix it ? – Dev George Apr 02 '18 at 11:46
  • As @MK_ has already commented, here is how you do it: https://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql/156813#156813 – MJH Apr 02 '18 at 12:08

0 Answers0