List top 20 rows for each table in excel (over 600 tables)

Toad Data Point
1

Is it possible to list top 20 rows for each table in excel (over 600 tables). trioed automation to hgenerate table list: SELECT SCHEMA_NAME(schema_id)+'.'+name
AS SchemaTable
FROM sys.tables

but cannot use in a select top 20 * from the results in automation. Any alternate approach that may work?

Thanks Dave

2

I would still try using Automation. I would use a loopDataset activity.

The driving query would list the name of the name with schema reference. Something similar to what you are showing above.

Then use the loopdataset variable for that row to build your TOP 10 sql.
SELECT SCHEMA_NAME(schema_id)+'.'+name
AS SchemaTable
FROM sys.tables

Then add Select to file as a child activity and set the sql as below.
select TOP 20 * from #Loop_data_1_SQL.SchemaTable#

The #Loop_data_1_SQL.SchemaTable# is replacement syntax and will be replaced with each row value for SchemaTable.

The Select file options should be to append a worksheet for every export and name the worksheet the name of the table. I attached a sample. Change the extension to "tas". You will need to change the connect info to run.

Top 20 rows.txt (15.6 KB)

3

HI Debbie,

Thanks for the prompt response - it is greatly appreciated! I realise where i came unstuck - I thought that the loop variable had to have a colon at the start as in the blog ' :Loop_data_1_SQL.REGION_NAME)' and could not work out how to resolve. Thanks again.

Dave

4

The colon as used above works as a traditional database variable where the value is bound on the server. Variables can be used in filters and as column values but not as table names. They definitely have their use just not in this case. A literal replacement does better.

5

thanks debbie - appreciate your help!

6

I could do with each excel sheet showing the tablename either in the tab or in a col - any ideas on how this can be accomplished

7

The script I posted shows you how to put the table name in the worksheet tab. In the Excel options there is a worksheet name option. Enter #Loop_data_1_SQL.SchemaTable# there

8

HI Debbie, Thanks - appreciate your help.

My Best Dave