Link to home
Create AccountLog in
Avatar of Ted Penner
Ted PennerFlag for United States of America

asked on

SQL statement needed for MariaDB

I have a SQL query console window opened within my IntelliJ IDE and am able to type select and other statements from there against my MariaDB database.


I need a statement that will create output that I can paste into a shared spreadsheet.

The output from the statement should answer the following questions:


  • What is the name of the default schema that the command is being run against?
  • What are the names of each table?
  • How many records does each of the tables contain?
  • What are the names of each of the fields within each table?



Avatar of kenfcamp
kenfcamp
Flag of United States of America image

Sounds like class work..
Hi,

Try these queries

    What is the name of the default schema that the command is being run against?
select database();

Open in new window


    What are the names of each table? How many records does each of the tables contain?
     
      select table_name,table_rows 
      from information_schema.tables 
      where table_schema = (select database())
      

Open in new window

     Note that to have accurate number of rows for each table stored in the catalog you need to run analyze table command on each table. And doing so regularly will help your database to achieve optimal performance and you as a DBA to manage/change the tables as the data changes and grows.
https://mariadb.com/kb/en/analyze-table/

    What are the names of each of the fields within each table?
       
        select table_name,column_name,ORDINAL_POSITION  
        from information_schema.columns 
        where table_schema = (select database())
        order by table_name,ORDINAL_POSITION
        

Open in new window


For more info on how to query the catalog for table information as well as other information see this link
https://mariadb.com/kb/en/information-schema-tables/


Best regards,
     Tomas Helgi
Avatar of Ted Penner

ASKER

Not quite getting there.

This is the screencast that shows what happened when I tried each of the three blocks of code
https://www.screencast.com/t/q9CRvK7BZR
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Well if select database() returns null, the others are guaranteed to return null since the both use database().

If database returns null, you aren't currently using a database:
https://mariadb.com/kb/en/database/

In that link there is a test database and you need to issue:
USE test;

Once you are inside a specific database, the queries for for me in 10.3 but that is as far back as this fiddle site goes:
https://dbfiddle.uk/RInE1myf

i marked above commands as suggestion/helpful

for the sake of completeness, here are the simpler mysql/mariadb commands.

the above will work on all dbs but are a pain to type and remember.


  • What is the name of the default schema that the command is being run against?
  • What are the names of each table?

show tables

note this returns an explicit "no database selected" error if you did not select a db

  • How many records does each of the tables contain?

select count(*) from TABLE

internal counters will be updated if needed and the correct number of rows is always returned.

nb : this takes a read lock on all the table rows while the information schema way is lock free

  • What are the names of each of the fields within each table?

explain TABLE

or

show fields from TABLE

... which also return the fields types and length


it is not uncommon to create aliases such as "ls" for such commands

Hi,

When you connect to a database the "select database();" will show the database currently connected to.
When you issue the command "use <databasename>;" the current database is changed to that particular database.

If select database() returns null you are probably connecting to a database instance using the command line tool which allows you to connect to an mysql/mariadb instance without specifying a database name hence the null.

If you connect to a database using a JDBC driver or other language MySQL/MariaDB drivers (or from tools like Dbeaver) you need to specify a database and that database becomes the default database which you are connected to. From there you can change the database using the use database (as long as you have enough privileges to do so).

Best regards,
    Tomas Helgi
Not quite getting there.

Right, you're getting "null" because you didn't tell it which database to use

Try changing
where table_schema = (select database())

Open in new window


To
where table_schema = 'your-database_name'

Open in new window

This code below from skull* did result in a list of "Tables_in_cars" which shows the name of the current database, as well as the names of the tables it contains.

show tables

Open in new window

Now if I want to show how many records and which fields each of the resulting tables contain, how would I request that within the same block of code?

information schema is more suitable for that. you can easily join the data from the tables and column tables to produce whatever output  you expect. you should browse said tables. it is quite easy to figure out.


select table_name,table_rows,group_concat(column_name) from tables join columns on ... where schema = 'YOURDB' group by table_name


the above is a skeleton. i am on a mobile phone. the fields and tables names are wrong but that should help you get there.

show tables

Open in new window

I want to expand the "show tables" statement above so that it also shows how many records and which fields are in each of the resulting tables.

How would I request that within the same block of code?

i guess you already figured that out but you can't do that in a single query.


you can list fields with

show fields from TABLEX


you can grab the number of rows with

show table status like TABLEX

this statement will work with multipLe tables

show table status from   DBX

and you can use where clauses


but show statements are not join friendly except possibly in very recent versions.




Still, that is the goal to get a single statement that gives me that information
Hi Ted,

Have you looked at the queries that I provided in my first comment?

Those queries give you the information you are looking for.

You can easily adjust the where clause and add colums to the select clause too your needs ( see further info in the provided links in that same comment ).

Best regards,
   Tomas Helgi
Let me know what single command to paste and I will test it again.

I want to expand the "show tables" statement so that it also shows how many records there are in each resulting table and which fields are in each of the resulting tables.

How do I request all of that within the same block of code?
Still, that is the goal to get a single statement that gives me that information

IMO that's not going to be possible. You're looking for results on table information for a given database
and results on column information for tables within a given database.

To get what you're looking for will require two separate queries
one against information_schema.tables, and another against information_schema.columns
ok, what are they?
To simplify what Tomas provided already

select table_schema, table_name, table_rows from information_schema.tables where table_schema = 'your database name';

Open in new window


Will provide database schema (name), table names and row counts for tables

More information can be found here
https://mariadb.com/kb/en/information-schema-tables-table/

select table_schema, table_name, column_name FROM information_schema.columns where table_schema='your database name';

Open in new window


Will provide database schema (name), table names and and column information for each table

More information can be found here
https://mariadb.com/kb/en/information-schema-columns-table/

i gave you the skeleton single query above. is there any thing hard to figure out ?

besides the fact table names and fields are capitalized, there should not be anything complicated.

please give it a try. if you do not get it, i will help but i usually answer from my mobile phone which has no mysql nor any other convenient means to test queries

got a mysql around this time

this is your query. i included the fields type as well.


select TABLES.TABLE_NAME,TABLE_ROWS,group_concat(COLUMN_NAME,'(',DATA_TYPE,')') from TABLES join COLUMNS on TABLES.TABLE_NAME = COLUMNS.TABLE_NAME and TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA where TABLES.TABLE_SCHEMA like '%' group by TABLE_NAME

Skull, I can't wait to try that. Thank you!
This is what I got back so far from the statement below that Skull* posted
select TABLES.TABLE_NAME,TABLE_ROWS,group_concat(COLUMN_NAME,'(',DATA_TYPE,')') from TABLES join COLUMNS on TABLES.TABLE_NAME = COLUMNS.TABLE_NAME and TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA where TABLES.TABLE_SCHEMA like '%' group by TABLE_NAME

Open in new window

Screencast: https://www.screencast.com/t/7U1E4SbxvN
The TABLES and COLUMNS in the FROM clause are in the information_schema.

Try fully qualifying:
select TABLES.TABLE_NAME,TABLE_ROWS,group_concat(COLUMN_NAME,'(',DATA_TYPE,')') 
  from information_schema.TABLES join information_schema.COLUMNS on TABLES.TABLE_NAME = COLUMNS.TABLE_NAME and TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA 
  where TABLES.TABLE_SCHEMA like '%' group by TABLE_NAME

Open in new window


I don't know what tool you are using to query the database but I would suspect the 'no database' error from the latest video is similar to what we mentioned above:  You haven't issued a 'use cars;' prior to running that query.
You haven't issued a 'use cars;' prior to running that query.

This is true. I can try that but the point of what I am trying to achieve is to not have to specify anything that is specific to any particular use case.
To see the tables and columns in a database, one has to be using that database.

I don't believe there is a way to query all tables in all databases from a server level BUT I also don't know very much about MariaDB/MySQL.

this query works without using a specific db if you prefix every field with the adequate db which is information_schema


select information_schema.TABLES.TABLE_NAME ... 

 just add information_schema everywhere.


beware you need to specify each field using db.tbl.fld syntax so you will also need to explicit the table name where i did not.



This code returned data in these three columns
select TABLES.TABLE_NAME,TABLE_ROWS,group_concat(COLUMN_NAME,'(',DATA_TYPE,')') 
  from information_schema.TABLES join information_schema.COLUMNS on TABLES.TABLE_NAME = COLUMNS.TABLE_NAME and TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA 
  where TABLES.TABLE_SCHEMA like '%' group by TABLE_NAME

Open in new window

User generated imageThis is not exactly the output I was looking for but is closer. I did not have to do "use cars" although maybe that is better. Still not sure, but definitely seems closer. At least we have some output. The first column should probably show the database name.

this return 3 field

table name

num rows (based on stats so can be a little off)

comma separated field list and type


i do not know what you want exactly but i believe you should be able to adjust.


if you just want the field without types, change the group_concat as needed.

This returns 3 field table name num rows (based on stats so can be a little off) comma-separated field list and type I do not know what you want exactly but I believe you should be able to adjust. if you just want the field without types, change the group_concat as needed.
Please suggest a replacement code block for me to try as I am not still getting quite the output that I was wanting per my response above, but it is becoming clearer so here is an updated objective.

Output columns:
Database - the name of the database
Table - table name
Rows - number of rows
Fields - fields with data types for each, separated by commas

So far, we are getting these columns
User generated imageThis is the code used so far and I am using the IntelliJ IDE to test it.
select TABLES.TABLE_NAME,TABLE_ROWS,group_concat(COLUMN_NAME,'(',DATA_TYPE,')') 
  from information_schema.TABLES join information_schema.COLUMNS on TABLES.TABLE_NAME = COLUMNS.TABLE_NAME and TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA 
  where TABLES.TABLE_SCHEMA like '%' group by TABLE_NAME

Open in new window

Might I suggest that we work from a common area so we are all looking at the same things?

Here is a simple Fiddle that creates a table with two columns and inserts two rows:
https://dbfiddle.uk/Sw57lPZt

Using skullnobrain's query and looking for that table in the results it returns:
TABLE_NAME	TABLE_ROWS	group_concat(COLUMN_NAME,'(',DATA_TYPE,')')
bob			2	col2(int),col1(char)

Open in new window


What is the output you would like to see?
ASKER CERTIFIED SOLUTION
Avatar of Tomas Helgi Johannsson
Tomas Helgi Johannsson
Flag of Iceland image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I'll try your modification next, Tomas but can you put it into a single statement since I won't have much time to test when I go to try it again.

Thanks very much for the collaboration on this.
@slightwv (䄆 Netminder)
I stand corrected, it can be done ;)

The code provided by @slightwv (䄆 Netminder) does seem to work well, however there are a couple of trade offs

First: The use of
where TABLES.TABLE_SCHEMA like '%'

Open in new window

means that results from every database including those used by MariaDB (MySQL) will be displayed

If this is desirable, then I'd recommend adding the string as suggested by @Tomas
TABLES.TABLE_SCHEMA,

Open in new window

This will provide the database name next to the table name identifying which DB it belongs to.

If you only want one database searched, this can be done by defining the Database to be used by replacing
where TABLES.TABLE_SCHEMA like '%'

Open in new window


With
where TABLES.TABLE_SCHEMA = 'YOUR-DATABASE_NAME

Open in new window


The second is more of a cosmetic issue which may or may not be a issue.
Depending on how you plan on displaying the results, additional formatting (wrapping etc) may be needed if a table has a lot of columns.

Results can become cumbersome to read especially when multiple databases and/or multiple tables with large column counts are involved
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account

do not use "having" for no reason. a where clause is more efficient.


i was expecting you to replace the "like %" with something meaningful to you. if you want all dbs, just remove the clause entirely. the cost is nil, though.


you can alias the selected field with the AS keyword as in any regular query.

Follow-up question

https://www.experts-exchange.com/questions/29265004/Expand-SQL-statement-to-include-the-last-date-and-time-that-each-table-was-changed.html

There used to be a feature in Experts Exchange that made it much easier to post a follow-up question that would automatically include a link back to the original question which also had the impact of notifying the prior experts of the new question.