CODEWITHSUNDEEP
sqlsql-server
jparanich
jparanich

Reputation: 9024

Select first N rows of all non-empty SQL Server tables

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

Answers (2)

Gordon Linoff
Gordon Linoff

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

serge
serge

Reputation: 1022

You can use sp_MSforeachtable function

EXEC sp_MSforeachtable 'PRINT ''?''; SELECT TOP 3 * FROM ?'

Upvotes: 1

Related Questions