SQL Server – Foreach Table

by
LinkedIn0

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 ?'

Code language: plaintext (plaintext)

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

Code language: plaintext (plaintext)

This outputs all of the table names and their record counts.

SQL query results showing record counts for all tables

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'

Code language: plaintext (plaintext)

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.

LinkedIn0

Leave a Comment