How to use sys.sp_MSForEachTable stored procedure of mssql in order to delete all data

[guntribam]

Hi. I'm trying to use a seeder to delete all data from my tables in order to seed with new test data.

import BaseSeeder from '@ioc:Adonis/Lucid/Seeder'
import Database from '@ioc:Adonis/Lucid/Database'

export default class DeleteSeeder extends BaseSeeder {
  public async run() {
    console.log(Database)
    await Database.knexRawQuery(`
      EXEC sys.sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
      EXEC sys.sp_MSForEachTable 'DELETE FROM ?'
      EXEC sys.sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
    `)
  }
}

It breaks with this message

❯ error     database/seeders/DeleteSeeder
  
      EXEC sys.sp_MSForEachTable 'ALTER TABLE @p0 NOCHECK CONSTRAINT ALL'
      EXEC sys.sp_MSForEachTable 'DELETE FROM @p1'
      EXEC sys.sp_MSForEachTable 'ALTER TABLE @p2 CHECK CONSTRAINT ALL'
     - Incorrect syntax near '@p2'.

Any tips to make this kind of sp work on adonis/knex?

[Julien-R44]

Hey 👋 I don't know Microsoft SQL Server at all, but here it looks like the problem is your question marks. Knex interprets ? and ?? as positional bindings. So you have to escape them like this :

EXEC sys.sp_MSForEachTable 'ALTER TABLE \\? NOCHECK CONSTRAINT ALL'

We can now see that the interogation point is well escaped :
https://runkit.com/embed/zv95i4w0cu5e

[guntribam]

Thank you so much ... that was the problem!

[RubTalha]

Unfortunately, sp_MSforeachtable is not available for Azure SQL https://stackoverflow.com/a/69741957/13086128