Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
You've got your answers here. Does any of them satisfy this question and does what you requested? If yes, mark it as the answer. –  Marian Feb 27 '13 at 9:25
    
@Marian, actually both of them satisfy my question as one produce a script while another using stored procedure. Just wondering if it is possible to use just sql statement to generate rather than sql that produce a script or using stored procedure.) –  Jack Feb 27 '13 at 9:29
    
Well, @Jānis's answer is the start that will work for you. You need to expand it further with building a dynamic sql with all those statements in a single nvarchar variable and execute it with sp_executesql. –  Marian Feb 27 '13 at 9:35
    
@Marian can we please not bully people into accepting an answer within a few hours of posting the question? What does that accomplish? The OP obviously knows how to accept answers. –  Aaron Bertrand Feb 27 '13 at 21:14
    
@AaronBertrand: didn't want to be a bully, sorry for that :-). But at that point the OP had two similar questions open and my first thought was the same as Jon Seigel's in the other question. Justice has been done, after all! –  Marian Feb 28 '13 at 9:28

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer

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 ?'
share|improve this answer
    
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

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'
share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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