Select * from * : Quick Tips

What? You want to see all of your data? Well do I have the script for you! New, Improved, Extra Colorful! here we have the one script to rule them all!!... I jest. Please never run this in production.


--all databases
exec sp_MSforeachdb @command1 = '
use [?]
begin
declare @dbname as varchar (128)
set @dbname = DB_NAME()
SELECT ''SELECT * FROM ['' + @dbname + ''].['' + SCH.name + ''].['' + TBL.name + '']'' as selectall
from sys.tables TBL
inner join sys.schemas as SCH on sch.schema_id = TBL.schema_id
end
'

--single database
SELECT 'SELECT * FROM [' + SCH.name + '].[' + TBL.name + ']' as selectall
from sys.tables TBL
inner join sys.schemas as SCH on sch.schema_id = TBL.schema_id

I've found an actual use for this... I had to look pretty hard though.

i use the version below to quickly fill out some DMV's, primarily the dm_db_index_usage_stats. Until there's some action on it, it has no usage to record.


--select top 1 for all databases

exec sp_MSforeachdb @command1 = '
use [?]
begin
declare @dbname as varchar (128)
set @dbname = DB_NAME()
SELECT ''SELECT TOP 1 * FROM ['' + @dbname + ''].['' + SCH.name + ''].['' + TBL.name + '']'' as selectall
from sys.tables TBL
inner join sys.schemas as SCH on sch.schema_id = TBL.schema_id
end
'

Regardless, you can now select * from *. Have fun!