6

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?

9

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.

  • This works well. What is the best way to export all of the results as one file? For example, I have 15 tables, so I now have 15 sets of data that were returned. – user58700 Feb 27 '17 at 23:32
  • 1
    @systech Well, do you have 15 tables with the same structure? If so, you could build a UNION ALL. If not, then you could use an app to do it, or concatenate everything into a big ugly string, but what would you be doing with a file that had data from 15 different tables in it? – Aaron Bertrand Feb 28 '17 at 0:08
  • I have inherited a database that has 15 tables, and I would like to see the first 10 or so rows of each table. The above code helps with that, but I would like to export them all to one document or file, in order to get an idea of what each table contains. – user58700 Feb 28 '17 at 6:13
  • thanks, I stole from your answer to make my question : dba.stackexchange.com/questions/194859/… – Arjang Jan 8 '18 at 6:13
5

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 ?'
  • How do I let it shows the name of the table that the records is showing – Jack Feb 27 '13 at 8:12
  • @Jack I updated my answer, the second query retrieves table names too. – Maryam Arshi Feb 27 '13 at 8:24
  • I'd be careful about using undocumented, unsupported procedures like this. I have pointed out serious problems with sp_MSforeachdb, which will never be fixed, and I believe it is possible for the same symptoms to appear in sp_MSforeachtable under slightly different circumstances. – Aaron Bertrand Feb 27 '13 at 20:19
  • @MaryamArshi, Sorry to untick your solution because Aaron Solution is much better but I have upvote you. – Jack Feb 28 '13 at 1:02
  • 2
    @Jack The main idea of being here is to learn and teach best solutions, so I am always happy to know there is a better solution than mine. – Maryam Arshi Feb 28 '13 at 5:40
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'
  • Why not sys.tables which doesn't need the type filter? – Aaron Bertrand Feb 27 '13 at 20:17
  • @AaronBertrand if you like so.. – Jānis Feb 28 '13 at 8:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.