How to select the first 10 records for each table in the same database using sql

Question

I have 50+ tables in a database.

I don't want to use the following SQL on each table in a database.

select * from {table_name}

So, is it possible to display the first 10 records for each table inside the same database using sql?

Answer 1

I'd be careful about using sp_MSforeachtable, as it uses the same code pattern as sp_MSforeachdb. I've pointed out the problems with sp_MSforeachdb here and here and was told it wouldn't be fixed here.

Instead I would do something like this (which also includes the table name in the output, probably useful):

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'

SELECT TOP (10) [table] = ''' + REPLACE(name, '''','') + ''', * 
    FROM ' + QUOTENAME(SCHEMA_NAME([schema_id]))
  + '.' + QUOTENAME(name) + ';'
FROM sys.tables AS t;

PRINT @sql;
--EXEC sp_executesql @sql;

When you're satisfied with the PRINT output (note that it will truncate at 8K), uncomment the EXEC.

Answer 2

Use sp_MSforeachtable

EXEC sp_MSforeachtable 'select top(10) * from ?'

To show the table Name you should change it like this :

EXEC sp_MSforeachtable 'select "?" as table_Name , * from ?'

Answer 3

sp_MSforeachtable is great. but if you want to somehow limit which tables to look at (for example, just 'dbo' schema) you can generate select scripts like this:

select 'Select Top 10 * From ' + SCHEMA_NAME(schema_id) + '.' + name 
from sys.objects 
where type = 'U' -- and SCHEMA_NAME(schema_id)  = 'dbo'