EXEC sp_MSforeachtable @command1 = “DROP TABLE ?”
This is a hidden SP in sql server, this will be executed for each table in the database you connected (you cant rollback this)
If u want to delete it from the command prompt try this
EXEC xp_cmdshell ‘SQLCMD -U <user> -P <password> -Q ‘EXEC sp_MSforeachtable @command1 = “DROP TABLE ?” ‘ ,no_output
Delete/truncate all the Data from each table for in the database you connected
EXEC sp_MSforeachtable @command1 = “DELETE FROM ?”
EXEC sp_MSforeachtable @command1 = “TRUNCATE TABLE ?”
I too explain it now, as sp_MSforeachtable is Stored Procedure, that will execute for all the tables for database & @command1 is variable which will run against each table for connected database, now whatever you will write in the double quotes, that will be act as a command for each table, where ‘?’ is the name of the table.
try this, it will clear your comcepts
EXEC sp_MSforeachtable @command1 = “SELECT * FROM ?” — Selects all the rows form all the table
EXEC sp_MSforeachtable @command1 = “PRINT ‘?'” –Just print the tables names with owner(dbo)
For more understanding, go for the MSDN or google, this is the right way.
Link to source