Today we will go over very simple script which will list all the tables for all the database.
sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'
Update: Based on comments received below I have updated this article. Thank you to all the readers. This is good example where something small like this have good participation from readers.
Reference : Pinal Dave (http://www.SQLAuthority.com)
pinaldave
LOL!
It took me half of a dozen lines to come up with what your one liner achieved!
Good one!
-Marlon Ribunal
Hi Pinal,
Shouldn’t we be using the information schema views in Sql 2005 and onwards?
sp_msforeachdb @command1=’USE ?;SELECT * FROM INFORMATION_SCHEMA.Tables’
Nice post Pinal!
You can avoid the “USE” statement by using three part object name. For example:
sp_msforeachdb ’select * from ?.sys.tables’
And some times it is important to know which database is being processed and the following might be helpful.
sp_msforeachdb ’select ”?” AS db, * from ?.sys.tables’
Thank you Jacob Sir!
Is there a way to use this query for auditing DMl events
Thank you ,
Sometimes a database may contain the name space
sp_msforeachdb ’select “?” AS db, * from [?].sys.tables’