topic Re: Generate a report of database schema and sample data from each table in Discussions

Generate a report of database schema and sample data from each table

Momentum — Wed, 13 Dec 2023 22:31:39 GMT

Is there a way I can generate a report that shows me all the tables in my database along with all of the columns of each table and the first 1-2 rows of each table (I may recognize the actual data better than the name of them column)?

Bonus

Because some tables have many columns and it is easier to scroll down and print down than sideways, is there a way to make this report such that the first 1-2 rows of each table are displayed as a column along with the columns name instead of rows (like a transpose but this couldn't (because data types would not be consistent) and need not generate an actual JMP table (just a displayed report)?

Lets use the Big Class sample data set as an example of one table. I would want to get a 5x2 or 5x3 matrix that looks like this:

Column Name, Row 1, Row 2;

Name, Katie, Louise;

Age, 12, 12;

Sex, F, F;

Height, 59, 61;

Weight, 95, 123;

Re: Generate a report of database schema and sample data from each table

txnelson — Wed, 13 Dec 2023 23:16:34 GMT

I am not aware of a report like you have suggested. However, here is a simple script that might give you a start on developing ove.

Names Default To Here( 1 );
dt = New Table( "Table of Contents",
	New Column( "Table", character ),
	New Column( "Variable", character ),
	New Column( "Row 1", character ),
	New Column( "Row 2", character )
);

table = 1;
While( Try( Data Table( table ), "" ) != "",
	tableName = Data Table( table ) << get name;
	colNames = Data Table( table ) << get column names( string );
	If( tableName != "Table of Contents",
		For Each( {col}, colNames,
			dt << add rows( 1 );
			dt:Table[N Rows( dt )] = tableName;
			dt:Variable[N Rows( dt )] = col;
			If( N Rows( Data Table( table ) ) >= 1,
				dt:Row 1[N Rows( dt )] = Char( Column( Data Table( table ), col )[1] )
			);
			If( N Rows( Data Table( table ) ) >= 2,
				dt:Row 2[N Rows( dt )] = Char( Column( Data Table( table ), col )[2] )
			);
		)
	);
	table++;
);

Re: Generate a report of database schema and sample data from each table

Momentum — Sun, 24 Dec 2023 23:17:58 GMT

Dear @txnelson , I have never scripted before. As such I cannot understand this script by reading it. However, when I paste it in and run it, I see that it gives me the summary table I wanted taking the data from every table I have currently open in JUMP. Is that correct? If so, it is wonderful. All I need now is to open all the tables in my database. We only have 80 so it shouldn't be that bad. In sum, please answer:

1) Am I correct about what that script does, and

2) Do you know how to open all the tables in a database? If not, I can either find it in help or open another chat/ticket.

Thanks again.

Re: Generate a report of database schema and sample data from each table

txnelson — Mon, 25 Dec 2023 03:10:26 GMT

You are correct.
You use the term "database". That is an ambiguous term. Can you be more specific as to what you are referring. Is it an a commercial database like Oracle or is it a collection of JMP tables stored on a remote file server, or something else?
You need to take the time to start to learn programming and specifically JSL. If you are going to be using JMP scripts, for your own security. You need to be able to validate any JSL you receive. Over time, you also need to become more self sufficient in using JMP and using JMP includes being able to write scripts. The Community is here to help you with issues, but it is not here to write scripts on demand.

Get back to me on what the definition of "database" is in your environment, and I will try to point you in the direction to get them all open in JMP.

Re: Generate a report of database schema and sample data from each table

Momentum — Tue, 26 Dec 2023 23:12:36 GMT

@txnelson , Thanks for the heads up on using scripts without understanding them. That makes sense.

By database, I mean that our company has a postscript SQL database hosted on AWS. We have a part time developer who designed and maintains that database. I am able to access the tables and views inside of that database from JMP due to an ODBC driver that our IT person installed on my computer. I can even write SQL inside of JMP to query it, although I may prefer to use JMP's table level joins instead, but I digress as that is another topic of discussion.

Re: Generate a report of database schema and sample data from each table

txnelson — Wed, 27 Dec 2023 02:55:07 GMT

Using SQL within JMP, you can query your database to return a list of all of the tables in the database. Then using the returned data table, you can loop through the table, opening up each of the tables.

Re: Generate a report of database schema and sample data from each table

Momentum — Wed, 27 Dec 2023 05:25:30 GMT

Thanks. This all sounds great. I cannot accept as solution until I try it, but it sounds like it is indeed the solution.

Re: Generate a report of database schema and sample data from each table

pmroz — Thu, 28 Dec 2023 13:36:00 GMT

I suspect that you have a PostgreSQL database (not postscript). A quick google search reveals this query to get all of the table names for the schema xxx:

SELECT table_name FROM information_schema.tables 
 WHERE table_schema = 'xxx' AND table_type = 'BASE TABLE'