Solved

Show all tables with contents in a db

  • 30 April 2024
  • 3 replies
  • 33 views

Badge +1

Hi all,

I am trying to show all tables with contents in a database. I have a database with over 800 tables. Some tables are empty, some are not. 

 

Im using this query in a sql client:

SELECT SCHEMA_NAME(t.[schema_id]) AS [table_schema]
          ,OBJECT_NAME(p.[object_id]) AS [table_name]
          ,SUM(p.[rows]) AS [row_count]
    FROM [sys].[partitions] p
    INNER JOIN [sys].[tables] t ON p.[object_id] = t.[object_id]
    WHERE p.[index_id] < 2
    GROUP BY p.[object_id]
        ,t.[schema_id]
    ORDER BY row_count desc

 

 

If i am using a Sql Executor with this sql statement i have a return value of more than 300 tables wich is correct. In the preview of the feature window i see more than 300 lines. But i dont see any attributes like a table name or the number of records.

 

Do you know how i can expose these attributes?

icon

Best answer by redgeographics 30 April 2024, 12:45

View original

3 replies

Userlevel 5
Badge +26

The SQLCreator requires you to specifically expose the attributes coming out of it (the Attributes to Expose parameter), so in this case you would need to add table_name and row_count there.

Userlevel 6
Badge +34

Using aliases should do the trick. You probably have to expose table_schema, table_name and row_count in the SQLExecutor, or using an AttributeExposer. If that does not work, I would try to use quotes, not straigh hooks, but I’m not sure.

Badge +1
We're done. The "populate from sql" button doesn't give us any results. The window remains empty. Weird?But manually entering our desired attributes does work.Thank you redgeographics for this insight!

 

Reply