To execute a query against all tables in a database, you can use the sp_msForeachTable stored proc, like this:
EXEC sp_msForeachTable 'SELECT FROM ?'
Note: The ? is a placeholder for the table name.
In this article I’ll show two examples of using sp_msForeachTable.
Example – Get record counts for all tables
This query saves record counts to a temp table, then shows the counts.
CREATE TABLE #counts ( TableName VARCHAR(128), RecordCount INT ); EXEC sp_msForeachTable "INSERT INTO #counts (TableName, RecordCount) SELECT '?', COUNT(*) FROM ?" SELECT * FROM #counts
This outputs all of the table names and their record counts.
Example – Delete all rows from all tables
Here’s how to delete all rows from all tables. Notice that you have to disable constraints before deleting, then re-enable them after.
EXEC sp_msForeachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' EXEC sp_msForeachTable 'DELETE FROM ?' EXEC sp_msForeachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
Warning: Only do this if you don’t actually need the data, such as in a local dev environment.
I have the following two tables. The Games table has a foreign key constraint on the Users table.
Running this query successfully deletes all data from my tables.
Why do I need to disable constraints?
If I try running sp_msForeachTable with just the delete statement, I get the following error:
The DELETE statement conflicted with the REFERENCE constraint “FK_Games_Users”. The conflict occurred in database “TestDatabase”, table “dbo.Games”, column ‘UserId’.
This is because the Games table has a foreign key constraint on the Users table.
If you’re doing a delete the normal way, you’d have to delete data in the correct sequence. For example, I would need to delete data from the Games table first, then the Users table. The sp_msForeachTable stored proc does not guarantee the order that it operates on tables, which is why I simply need to disable all constraints before deleting.