Friday, April 3, 2009


April 03, 2009 Posted by Jason Irwin No comments

I came across an interesting undocumented T-SQL command that I thought I would share. I wanted to refresh my testing environment and remove any tables in a given schema. My goal was to clear out everything before running my creation and data initialization scripts. Previously I have queried the INFORMATION_SCHEMA views in order to generate my delete queries, but the undocumented sp_MSForEachTable command provides a fantastic way of doing this in a single command (not counting disabling referential integrity…)

  1: -- disable referential integrity 
  2: EXEC sp_MSForEachTable 
  3:     @command1 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL", 
  4:     @whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = ‘my_schema’)" 
  6: -- drop tables 
  7: Exec sp_MSforeachtable 
  8:     @command1 = "DROP TABLE ? PRINT '? dropped'", 
  9:     @whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = ‘my_schema')"

In two commands I was able to disable referential integrity on and drop all tables in the my_schema schema. Similarly, to get the size usage details for every table in the same schema, I simply use the following command:

  1: Exec sp_MSforeachtable     @command1 = "EXECUTE sp_spaceused [?];", 
  2: @whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = ‘my_schema')"

A similar procedure exists to iterate through databases (sp_MSForEachdb) but I have not yet found an in-built version for views/procedures/etc.

Not rocket science, but a handy timesaver nonetheless!