Querying all tables in a database using sp_foreachtable

Last week one of my colleague was struggling to write a sql script.After some time he came to me.His requirement was simple, he need to truncate all the tables in the DB.He wrote a script.But it was throwing exception because of foreign key relation ships.My first answer was to run that script for 10 or 20 times so that the child tables will get deleted in initial runs and in primary key tables will get deleted in last runs.A real hack!!isn’t it? I suggested this because he need to just clear a database for one time.

But after some time I thought of solving it properly.For that I didn’t try writing a new script or correcting his script.Just googled and got the below link.
http://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql

It says 2 lines of code as follows.

-- disable all constraints
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- delete data in all tables
EXEC sp_MSForEachTable 'DELETE FROM ?'

Done.I again enforced the concept of modern programming especially in .Net and SQL server.ie “If you think your problem might be faced by somebody else, google it before even attempting”.


Also I would like to tell about the undocumented procedure sp_msforeachtable.Its very useful when you need to execute same operation in all the database tables.More details can be found in this link.Now a smart programmer you might have though whether there is anything for looping all the databases in the sql server instance.Yes its there.the name is sp_foreachdb.


Oh that is great.Everything related to db objects are available as for each?Unfortunately NO.But you can create your own foreach by following this article.Yes you can create your own  sp_foreachview,sp_foreachsp etc…I don’t think developers including sql developers need this very frequently.But the DBAs surely need this.

Happy scripting.