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)
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.
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.
exec sp_MSforeachtable 'select top 3 * from ?'
Here you have:
DECLARE @sql VARCHAR(MAX)='';
SELECT @sql=@sql+'SELECT TOP 3 * FROM '+'['+SCHEMA_NAME(schema_id)+'].['+name+']'+';'
FROM sys.tables
EXEC(@sql)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With