Reputation: 9024
I'm performing a database migration to a new ERP system, and the current Microsoft SQL database has well over 3000 tables, 95% of which have no data causing a lot of grief/noise in figuring out the schema.
Is there a way I can print the header and first three rows of data for every table that is non-empty from the database?
Thanks for the help!
Upvotes: 0
Views: 285
Reputation: 1271241
SQL Server has the unsupported sp_MSforeachtable
. So, you can do:
exec sp_MSforeachtable @command1='select top (3) * from ? where exists (select 1 from ?)';
Otherwise, you would be using a cursor or other looping mechanism to achieve basically the same thing.
EDIT:
Jeroen makes a very good suggestion:
exec sp_MSforeachtable @command1='if exists (select 1 from ?) select top (3) ''?'' as table_name, t.* from ? t';
This also adds the table name for extra spice.
Upvotes: 5
Reputation: 1022
You can use sp_MSforeachtable
function
EXEC sp_MSforeachtable 'PRINT ''?''; SELECT TOP 3 * FROM ?'
Upvotes: 1