Select everything from every table in every database

  • Article

[Caution: This is for testing only if you want to have a cached dataset, will have severe issues in production servers if you just executed it for trial]

 

We all know the undocumented (then it's not supported) procedure that executes a code in every database which is sp_msforeachdb

And its twin that executes against all tables in one database which is sp_msforeachtable but sometimes we want to mix them together and it's challenging because the replacement character in both is "? " But there's a way to change it and I thought it's nice to share

 

The sp_msforeachtable takes another parameter for the replacement character so you can change it

 

So this statement will work for one database

exec sp_msforeachtable 'select * from $', '$'

 

 

And this one will get you everything from ALL databases

exec sp_msforeachdb 'USE ?;exec sp_msforeachTable ''select * from !'',''!''';