Questions tagged [information-schema]
information schema : ODBC standard Views of RDBMS MetaData
130
questions
0
votes
1answer
16 views
Identify column name information from table schema [duplicate]
I have multiple tables with with varying names for the same piece of data. For example, day vs business_day. I'd like to identify what column names exist for which table. I think this can be done ...
0
votes
0answers
19 views
How to use a query to get the definition of (MariaDB 10.3.27) database objects (specifically the variables they use)
I have written a query to get most of the useful information about the routines in a MariaDB database for a database documenter that I am developing.
The code below will return routine name, routine ...
1
vote
1answer
35 views
SQL: How to get all tables' columns & keys + the primary key?
Database Info
MySQL 8.0.22
InnoDB
The Goal
I'm trying to get all tables in a database, with their column definitions and foreign keys, and primary keys on their own row for each table group.
Given the ...
0
votes
0answers
11 views
What is the differrence between mysql.innodb_table_stats and information_schema.innodb_tablestats?
I am using mysql-8.0.19. I want to find a way to detect changes in database tables. E.g. compare the value of a query to the value of the last query.
I see that there exist two tables from which I ...
0
votes
0answers
15 views
Does MySQL have a built-in way to tell whether a view is insertable (not just updatable)? E.g., something like the IS_UPDATABLE flag, but for INSERT?
The MySQL documentation for the INFORMATION_SCHEMA.VIEWS table mentions that MySQL sets a flag whenever a view is created that records whether that view is updatable, i.e., whether you can UPDATE the ...
3
votes
1answer
86 views
Unexpected result from PostgreSQL information schema
I have written a simple query that should show all table sizes of any given schema in human-readable format:
select table_name, pg_relation_size(quote_ident(table_name))
from information_schema.tables
...
0
votes
1answer
105 views
Is it possible to create Dynamic SQL statements purely in SQLite? [closed]
A lot of modern RDBMS have a feature called Dynamic SQL (MS SQL Server, for example) which allows you to build your SQL statements in strings and then execute that string of SQL.
Does such a feature ...
1
vote
1answer
42 views
How to inspect a view's structure in information schema in PostgreSQL?
Is there a way to discover the structure of a view in a similar way to how tables are described by entries in information_schema.columns? I can also use the pg_* tables.
In other words, similar to how ...
0
votes
0answers
9 views
how big is the server load when client hit information_schema for monitoring
when I create some small application monitor for mariadb, and then I send query like
select variable_value from information_schema.global_status where variable_name = 'THREAD_RUNNING'
or
select ...
0
votes
1answer
35 views
Memory Used On global_status mariadb
on mariadb at information_schema.global_status they have variable name memory_used in bytes
this is actual memory use from mariadb ?
edit add example case
on my servers i have threads_running=10 and ...
1
vote
0answers
92 views
Non-existent table that actually exists?
I've been tasked to solve a very difficult and interesting problem.
Given is a MySQL database created on the AWS-Aurora service. The MySQL version reported when connecting is 5.6.10.
We have an ...
1
vote
0answers
252 views
Delete INFORMATION_SCHEMA
I recently created a new database and INFORMATION_SCHEMA was there. I am aware that users should not mess with backend services, but INFORMATION_SCHEMA has 0 bytes. I checked all the tables and there ...
1
vote
1answer
76 views
In SQL Server, for numeric data type, can the CHARACTER_MAXIMUM_LENGTH be different from NULL?
I have a SQL Server with numeric types for few columns.
When getting a table structure, I have the following results:
Query:
SELECT *
FROM DB.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '...
0
votes
2answers
135 views
Select query on information_schema is too slow
SELECT *
FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
AND CONSTRAINT_NAME = 'FK_FOLDER_FILTER_CONTRACT_CATEGORY_CONTRACT_CATEGORY'
AND CONSTRAINT_TYPE = '...
0
votes
1answer
23 views
MYSQL - Run a query based on the results of a query
Trying to do some cleanup in the database, per manager request. However, I can't seem to get Mysql to run the inner query as written.
select @tbl:=column_name as'Table',
( select max(length(@...
0
votes
1answer
36 views
List foreign keys dependencies that refer to a given table
RDBMS: PostgreSQL 9.6
I desire to list the foreign keys for a given table and schema. I am about 90 % there but I also desire to know what options were used to make the fkey. Example on delete ...
1
vote
0answers
24 views
Are there any downfalls to not using foreign key relationships in a multi-tenary scheme?
We have a proposed restructure of our CRM containing almost 500 databases containing ~25 tables each into a single multidimensional database with one giant fact table linking everything together.
I ...
1
vote
1answer
530 views
Postgres view column references
Using the Postgres information_schema or system catalogs, is it possible to determine what table columns are referenced by the columns in a view?
information_schema.view_column_usage provides ...
1
vote
1answer
23 views
Does CUBRID have Information Schema Views?
I am trying to access the Information Schema views as defined in SQL92, and cannot find any in the CUBRID RDBMS. Has CUBRID implemented this specification and how can I access them?
CUBRID version: ...
0
votes
3answers
1k views
MySQL: list users which have been granted EXECUTE permission on a given procedure
I would like to audit my database to check which users have been granted individual EXECUTE permission to which procedure.
I would like to use this information to be able to save GRANTs when changing ...
1
vote
2answers
185 views
Predicting the primary keys exhaustion in MySQL based on rate of insertion
We are running out of primary keys for our table in production. We do not want that to occur again. So we need a mechanism to alert us when we are running out of primary keys based on the rate of ...
1
vote
1answer
433 views
Why do information_schema queries scan all databases?
I'm building a PHP application that looks up the last modified date of all databases using information_schema. A simplified version is like this:
$sql = "SELECT update_time FROM information_schema....
0
votes
1answer
35 views
How to select maximum date_created value from all tables matching certain pattern?
Given a MySQL DB with a hundreds of tables with the ones of interest being named ap_form_* where * = a random value. How do I loop through that list of tables to display the MAX(date_created) for each ...
1
vote
1answer
532 views
Why is the value of NUMERIC_SCALE column in INFORMATION_SCHEMA.COLUMNS table for DATA_TYPE='double' NULL?
I have created a column whose data_type is DOUBLE.
alter table my_table add column my_column double;
When I queried about this column in information_schema.COLUMNS table
I found that the ...
3
votes
2answers
6k views
GRANT SELECT ON SEQUENCE succeeds, but to no effect?
I am trying to grant select access to sequences from one user/role to another. There are no errors when I run the command but once run, the second role is unable to view the sequences. I have run the ...
1
vote
2answers
2k views
Information_Schema on oracle?
I'm a SQL Server DBA, and I like to mess around with Oracle here and there.
I can see with select * from information_Schema.columns everything about a table (everything that I need now).
Is there a ...
1
vote
3answers
142 views
Does MariaDB 5.5.56 have INFORMATION_SCHEMA.INNODB_METRICS?
I'm trying to debug why I don't have INFORMATION_SCHEMA.INNODB_METRICS. I'm uncertain because I see it in the MySQL 5.5 reference glossary: https://dev.mysql.com/doc/search/?d=10&p=1&q=...
1
vote
1answer
630 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
196 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
2answers
2k views
MySQL copy all views from one database to another in stored procedure
I've got a stored procedure that copies all tables from one database to another but steps through each table individually (because of queries).
I need to add to the stored procedure to copy all views ...
0
votes
1answer
123 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
...
10
votes
1answer
269 views
Why shouldn't INFORMATION_SCHEMA views be used to determine the schema of an object?
According to MS-DOCS about System information schema views, the schema columns definition have a warning note that says:
** Important ** Do not use INFORMATION_SCHEMA views to determine the schema ...
1
vote
1answer
845 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
1k 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
1k 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
...
1
vote
2answers
435 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
566 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
46 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 ...
5
votes
1answer
11k 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 ...
2
votes
1answer
5k 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
128 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
252 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
1k 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
57 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
5k 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))
1
vote
1answer
3k 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
44 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
596 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
...
2
votes
1answer
919 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
116 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 ...
