Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select the first 3 rows of each table in a database

I have a database with 69 tables and I want to select only the first three records of each table.

I can do it per table with:

SELECT TOP 3 * 
  FROM table_schema.table_name

However if I was to do this manually, it would take a lot of time.

Could you please suggest a workaround?

I tried this solution but I can get it to work (I don't know how to modify it for MSSQL)

EDIT Thanks for your replies. I probably wasn't clear enough: I meant I wanted to parse each individual table and only get the top 3 records than move on to the next one. Yaroslav's code below is what I needed

DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+'SELECT TOP 3 * FROM '+'['+SCHEMA_NAME(schema_id)+'].['+name+']'+';'
  FROM sys.tables
EXEC(@sql)
like image 640
Phil Avatar asked Jul 31 '12 13:07

Phil


People also ask

How do I get the first 3 in SQL?

You can use LEN() or LENGTH()(in case of oracle sql) function to get the length of a column. SELECT LEN(column_name) FROM table_name; And you can use SUBSTRING or SUBSTR() function go get first three characters of a column. Save this answer.

How do you select rows in a database?

To select rows using selection symbols for character or graphic data, use the LIKE keyword in a WHERE clause, and the underscore and percent sign as selection symbols. You can create multiple row conditions, and use the AND, OR, or IN keywords to connect the conditions.


2 Answers

 exec sp_MSforeachtable 'select top 3 * from ?'
like image 198
Aushin Avatar answered Oct 17 '22 11:10

Aushin


Here you have:

DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+'SELECT TOP 3 * FROM '+'['+SCHEMA_NAME(schema_id)+'].['+name+']'+';'
  FROM sys.tables
EXEC(@sql)
like image 23
Yaroslav Avatar answered Oct 17 '22 12:10

Yaroslav