Hi Community team, I have an requirement to fetch all the Schema Names and their associated 'Table Names with their Sizes' to display them in the appian interface. In order to achieve this, From the cloud database I have executed the following SQL statement which gives me the output: SELECT table_schema as `DB`, table_name AS `Table`, ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size` FROM information_schema.TABLES WHERE TABLE_SCHEMA IN ("Appian", "SchemaA", "SchemaB") ORDER BY `Size` DESC; On executing the result: EX DB Table Size Appian data 30.56 Appian example 28.52 ScheamA customer 3.52 Appian document 3.52 SchemA CaptureDetails 2.64 SchemaB Controls Library 2.52 Similarly, I'm trying to implement the same functionality in Appian using Query Database Smart Service in Process Model, on the set-up tab I'm able see only one schema(Selecting Pre-defined schema ) details: Instead of that I need to retrieve all the mentioned schema details. Kindly let me know any solution for this. Thanks in advance, Chandrasekhar

Question

Fetch Schema Names, Table Names and their Sizes

Forum|Forum|2 years ago
April 1, 2024
6 replies
0 views

Hi Community team,

I have an requirement to fetch all the Schema Names and their associated 'Table Names with their Sizes' to display them in the appian interface.

In order to achieve this, From the cloud database I have executed the following SQL statement which gives me the output:

SELECT table_schema as `DB`, table_name AS `Table`, 
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS `Size` 
FROM information_schema.TABLES WHERE TABLE_SCHEMA IN ("Appian", "SchemaA", "SchemaB") ORDER BY `Size` DESC;

On executing the result: EX

DB	Table	Size
Appian	data	30.56
Appian	example	28.52
ScheamA	customer	3.52
Appian	document	3.52
SchemA	CaptureDetails	2.64
SchemaB	Controls Library	2.52

Similarly, I'm trying to implement the same functionality in Appian using Query Database Smart Service in Process Model, on the set-up tab I'm able see only one schema(Selecting Pre-defined schema ) details:

Instead of that I need to retrieve all the mentioned schema details. Kindly let me know any solution for this.

Thanks in advance,

Chandrasekhar

davidj137213

Brainy

You will have to execute the smart service once for every schema in your database....

C

chandrasekharr5247Author

Hi [mention:04c48a6a061f4609acd71aebfc4be9ad:e9ed411860ed4f2ba0265705b8793d05]

Thanks for your response, by selecting the pre-defined schema it gives only that schema details. In my case I need all Schema's details.

davidj137213

Brainy

I know.... that's why you ned to create as much datasources as schemas in your environment, and execeute the smart service once for each one of them

shubhama926776

Brainy

Create multiple Query Database Smart Service activities within your process model, one for each schema you want to query.
Each activity will have a static SQL statement specific to the targeted schema.
After each activity execution, use a decision shape or appropriate logic to combine the retrieved data from each schema into a single list. You can achieve this by concatenating the output lists from each activity.

शुभम्

C

chandrasekharr5247Author

Hi [mention:dea33493db6342c9a73dc108d45120c0:e9ed411860ed4f2ba0265705b8793d05],

Appreciate your response, like need to use 'AND ' Gate append each schema details into the list and debug the process, correct?

And, We are looking for other alternatives ways to display them dynamically. Open for suggestions and solutions.

shubhama926776

Brainy

Yes Correct!!

Use separate Query Database Smart Services for each schema.
After each execution , append retrieved data to a single list using append
Display the final list containing combined data.

शुभम्

Sign up

Login with SSO

Login to the community

Login with SSO