Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

information schema : ODBC standard Views of RDBMS MetaData

1
vote
1answer
332 views

Is it possible to concatenate query results to form a new query?

I have now: SELECT TABLE_SCHEMA , TABLE_NAME , COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'NNN'; This gives me several hundreds of rows, because several tables contain ...
1
vote
1answer
58 views

Information_Schema.Tables reports different result in MySQL 5.5 and MySQL 5.7

I have started testing some MySQL 5.7 databases to replace our ageing 5.5 Databases, and there seems to be a significant change in the way the information_schema.tables Table records the data size. I ...
0
votes
1answer
37 views

Query to identify bad indexes and/or no indexes (on tables with lots of records) takes 4-6 minutes to execute

Query: SELECT Concat(t.table_schema, '.', t.table_name), t.table_rows, snu.non_unique, smax.cardinality, ( t.table_rows / Ifnull(smax.cardinality, 1) ) AS ...
1
vote
1answer
105 views

Where in pg_catalog is the database or catalog information stored about relations?

When I query a table like information_schema.tables SELECT table_catalog, table_schema, table_name FROM information_schema.tables; I get a result like this, table_catalog | table_schema ...
1
vote
1answer
114 views

How do I list all columns for a specified table that begin with a specific string [duplicate]

How to select all the columns whose names begins with "img" from a PostgreSQL table? I wrote that, but doesn't work select 'img%' from mytable
7
votes
1answer
723 views

Why does information_schema have “YES” and “NO” character strings rather than booleans?

I was taken by surprise to find that the is_nullable column for this query is typed as character varying (3) rather than boolean: select column_name, data_type, is_nullable from ...
0
votes
0answers
20 views

MySQL - Where i can find Innodb_history_list_length variable in new versions of mysql

MySQL version: percona-server-server-5.7 5.7.21-20-1.stretch What i get in new version of MySQL when i try execute the query: SELECT count FROM information_schema.innodb_metrics WHERE name = '...
1
vote
2answers
127 views

SQL Server 2016 Temporal tables metadata

Is there any way to get metadata (information scheme) information about temporal table feature for the table, like: Is DATA_CONSISTENCY_CHECK TURNED ON OR OFF What name of history table Is ...
0
votes
0answers
83 views

`SELECT` on `information_schema`.`tables` returns less rows if I `SELECT` the `CREATE_TIME`

Context We have a MySQL 5.6.28 server with a lot of schemas on it. After a crash, we have some schema where a table x exists, and other schemas where it's an orphan table. On a non-corrupted schema, ...
1
vote
1answer
32 views

Information_schema appearing in slow_log

I am finding that my information_schema keeps running this query SELECT engine, COUNT(*) as n FROM information_schema.tables WHERE engine is not null GROUP BY engine Which seems to always appear in ...
1
vote
1answer
2k views

postgres system catalog query columns from table

I am building an integration test against an app that provisions a database for a user. The User created for it is not a super user, and does not have access to schema_information.tables because when ...
1
vote
1answer
742 views

How do I query INFORMATION_SCHEMA.COLUMNS on Amazon RDS?

I received a list of column names from another team. To ensure all field column names are valid inside a specific schema, I put them into a temporary table. I'm now wanting to see if they exist. When ...
1
vote
0answers
57 views

mariaDB tables create_options clear

I have MariaDB 10.2.9 on Centos7 with ~60 tables. I listed the table's create_options from information_schema.tables: SELECT TABLE_NAME, TABLE_TYPE, CREATE_OPTIONS FROM information_schema.tables ...
0
votes
1answer
83 views

MySQL - “CREATE_OPTIONS” in “information_schema.tables”?

I have a MySQL 5.7.16 Enterprise running on a Centos 6 machine with InnoDB as the default storage engine. I have around 300 tables in which around 90 are "list" partitioned. I have the option ...
1
vote
2answers
297 views

Unable to join on conrelid and confrelid columns in pg_constraint

I'm trying to get a list of foreign key constraints in the following form: pk_table | pk_col | fk_table | fk_col without using pg_get_constraintdef (which would force me to resort to string ...
1
vote
0answers
35 views

error codes in information_schema

Is there a view or table or relation in information_schema with the error codes or exception names? My question is in STANDARD sql or in PostgreSQL.
0
votes
2answers
3k views

How to get the next auto-increment id in MySQL?

How to get the next id in mysql to insert it in the table ? INSERT INTO payments (date, item, method, payment_code) VALUES (NOW(), '1 Month', 'paypal', CONCAT("chaminda", id))
0
votes
0answers
399 views

How does the query table_schema=database() work?

In the query select table_name from information_schema.tables where table_schema=database(); as I understand; the information_schema.tables references to all the tables in the information schema ...
1
vote
1answer
405 views

Tables in one of my schemas are not listed in INFORMATION_SCHEMA.TABLES. What can be the reason?

I have three schemas in my SQL Server database, but when I query over INFORMATION_SCHEMA.TABLES only tables that belong to two of the schemas are listed. Tables in the third schema are not listed. ...
0
votes
1answer
41 views

How to locate all the tables that have a field named username

Is there a way in PhpMyAdmin to do a SQL search for all tables, that have a fieldname username in it? I am trying to help a friend that has a database with over 1,000 tables in it. He needs to find ...
1
vote
2answers
244 views

How do I navigate PostgreSQL documentation to find a list of all functions that work on a specific type?

Let's say I want to find a list of functions that can work on jsonb, what is the most effective way to do this from the terminal without using google? Assuming only access to man pages psql ...
1
vote
1answer
363 views

MySQL - huge Dictionary memory allocated

Our MySQL 5.5.38 servers have 1000-3000 InnoDB databases each (with about 100 tables each). All data is in single file. Most of DBs are active. While it's expected that information schema should be ...
1
vote
0answers
57 views

Schema transfer of SP does not update information_schema.routines.routine_definition / object_definition?

After a transfer of an SP from one schema to another, the information_schema.routines.routine_definition and object_definition() still show the original schema. create schema SchemaA; go create proc ...
3
votes
2answers
942 views

count null values of table from information_schema tables

Is there any way to count null values in a column by using information_Schema tables? EG: SELECT table_name, table_rows FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA ='myschema' and ...
0
votes
1answer
99 views

where are the infos from 'SHOW CREATE TABLE' coming from? It's not INFORMATION_SCHEMA

Quote from MariaDB 10.2 CHECK and DEFAULT clauses: Another limitation is that CHECK metadata are not accessible via the INFORMATION_SCHEMA. The only way to find out if a table has CHECK clauses ...
1
vote
2answers
33 views

Different services using different port pointing to same datadir folder

Let say I have already installed service MySQLa with mysqla.ini (port number=3306) setting and datadir folder in ...\data Is it possible then, I install another service MySQLb with mysqlb.ini (port ...
2
votes
1answer
497 views

Is there a way to get SQL Server capcity constraints dynamically

I have some code which creates database tables dynamically, and I want to ensure that people don't try to do things that result in me trying to create a table with more columns than what SQL Server ...
1
vote
0answers
28 views

What are all the catalogs that RDBMS have to implement to be ANSI compliant?

My goal is to be able to go into any environment, and immediately get a general idea of what is going on in a database server. I know most RDBMS implement INFORMATION_SCHEMA, which has a lot of useful ...
1
vote
2answers
1k views

Query to retrieve index details for a particular column in all tables

There is a column created_at in most of tables. I would like to get the details if that field exists in the table and it is not indexed. Is there any thing can be done with the information_schema to ...
0
votes
1answer
5k views

how to update information_schema.tables in mysql [duplicate]

i have a table A and i want to update the creation time of this table. I understand i cant simple update ( using update query) as it is not any table. Is there any way i can update it. for example ...
1
vote
1answer
72 views

What happens to mysql information_schema after switching to innodb_file_per_table?

We're switching our mysql dev deb instance to innodb_file_per_table and moving the data dir onto another mounted drive so we can free up space on our dev box. We've backed all our dbs up and about to ...
7
votes
1answer
650 views

What permissions are required to return rows from information_schema.schemata?

In postgres (I'm on 9.3.4) what permissions are required to return rows for select * from information_schema.schemata The 9.3 documentation says "are owned by a currently enabled role". Does that ...
0
votes
1answer
249 views

MariaDB: calculate the reads/writes ratio in percent

I found a blog post howto calculate the reads/writes ratio in percent. I receive NULL values in our env. > SELECT @total_com := SUM(IF(variable_name IN ('Com_select', 'Com_delete', 'Com_insert', '...
2
votes
1answer
769 views

Amazon Aurora CREATE_TIME in INFORMATION_SCHEMA.TABLES is NULL

We are running an Amazon Aurora database that emulates MySQL 5.6.10. The CREATE_TIME in SHOW TABLE STATUS and INFORMATION_SCHEMA.TABLES is consistently NULL for all our tables. Is this a bug, a ...
2
votes
0answers
202 views

Get the name of the table column referenced by a (auto updatable) view column (PostgreSQL)

Given the folowing view definition create or replace view projects as select id, client_id as clientId from data.projects Is it possible, knowing the view name and the view column to get the name of ...
1
vote
1answer
501 views

Does INFORMATION_SCHEMA always contain up-to-date metadata?

This query prints the size (in terms of data and indexes) of the tables in mydatabase: SELECT table_name "Table name", round(((data_length)/1024/1024),2) "Data size", round(((index_length)/1024/1024)...
0
votes
1answer
132 views

Query other database's INFORMATION_SCHEMA tables

I have a shared database that stores procedures for all the other databases on the same server. This helps maintain one instance of a procedure rather than copying it across all databases. As long as ...
3
votes
1answer
6k views

INFORMATION_SCHEMA.GLOBAL_VARIABLES alternative in 5.7? More info about show_compatibility_56

mysql> SELECT * FROM GLOBAL_VARIABLES; ERROR 3167 (HY000): The 'INFORMATION_SCHEMA.GLOBAL_VARIABLES' feature is disabled; see the documentation for 'show_compatibility_56' mysql> SELECT VERSION(...
0
votes
1answer
524 views

How do I join information schema to the table's data?

I need to join a table using it's column name from the INFORMATION SCHEMA. Is this possible? I actually found another way to work around it. I am using Innodb. For the structure it's kind of ...
1
vote
1answer
1k views

INFORMATION_SCHEMA.VIEW_TABLE_USAGE only shows tables from the same catalog

I'm working with a database that has many cross-database views. While INFORMATION_SCHEMA.VIEWS lists all these views, INFORMATION_SCHEMA.VIEW_TABLE_USAGE and INFORMATION_SCHEMA.VIEW_COLUMN_USAGE do ...
0
votes
1answer
49 views

Working with PostgreSQL information_schema and tables (with same names, in different schemas) whose names start with a number

I'm needing to work with tables whose names start with numbers. For example, in the crs_1973 schema there is a table called 2015_04_21. I know that when the table name is made up of numbers, I must ...
1
vote
0answers
70 views

MySql - view out params of stored procedure when it selects from table

I have stored procedure: DELIMITER // CREATE PROCEDURE prcGetFemaleUsers() begin select firstName, lastName from tblUser where gender = 'female'; end // DELIMITER ; How can I get types ...
9
votes
4answers
22k views

Calculate row size and max row size for a table

Problem: Is there any way of calculating number of bytes occupied by the table creation, i know that you can get some information from information_schema.tables but that information is not accurate ...
1
vote
0answers
15 views

Query INFORMATION_SCHEMA for list of indexes [duplicate]

How does one query INFORMATION_SCHEMA in a standards-compliant database such as Postgres to obtain a list of all indexes in one database/catalog?
3
votes
1answer
2k views

Which databases are backed up by “mysqldump --all-databases”?

Which databases are included in the dump generated by mysqldump --all-databases? By my experience, that should be all the user-created databases, plus the mysql database. The database ...
2
votes
2answers
370 views

Information schema not showing reliable information?

I'm currently migrating from SQL Server to MySQL. When i try to match the number of rows i query information_Schema.tables. I noticed that there are some differences between the migrated information ...
3
votes
2answers
9k views

How can I Search for a string in all fields across all tables of a MySQL database?

I want to find a string and I don't know exactly which column and which table. How can I find it? Thanks.
2
votes
2answers
8k views

SELECT * FROM INFORMATION SCHEMA on V5R3M0

I have access to a client's V7R1 AS/400 and I can run queries like SELECT NAME FROM INFORMATION_SCEMA.TABLES and SELECT COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, ...
13
votes
2answers
13k views

Query the definition of a materialized view in Postgres

I'm wondering how to query the definition of a materialized view in Postgres. For reference, what I hoped to do is very similar to what you can do for a regular view: SELECT * FROM information_schema....
3
votes
1answer
4k views

How to get row count from all tables of a schema ( without using information schema )

The query should collect data from table name , schema name from information.schema and row count should be taken from actual table.