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 integrity3: @command1 = "ALTER TABLE ? NOCHECK CONSTRAINT ALL",4: @whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = ‘my_schema’)"5:6: -- drop tables8: @command1 = "DROP TABLE ? PRINT '? dropped'",9: @whereand = "and uid = (SELECT schema_id FROM sys.schemas WHERE name = ‘my_schema')"10:
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')"3:
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!