Skip to main content

Questions tagged [information-schema]

information schema : ODBC standard Views of RDBMS MetaData

Filter by
Sorted by
Tagged with
0 votes
1 answer
21 views

Same global user works local but fails on live server for information_schema database UPDATE query

I have two database servers, local and live. The local server is running MariaDB 10.11.6, the live server is running 10.11.8. I have the exact same global user, example@localhost on both the local and ...
John's user avatar
  • 737
0 votes
0 answers
11 views

Aurora3 RDS MySQL Stored function/trigger performance when using information_schema

Since moving to Aurora3 RDS (MySQL 8 based) vs Aurora 2 (MySQL 5.7 based) noticed that execution time of certain triggers and stored functions performance has degraded. Correlation between triggers ...
BigKiwiDev's user avatar
0 votes
0 answers
83 views

INFORMATION_SCHEMA tables take so long when used in certain ways

While attempting to find better ways to parse table schema data, the INFORMATION_SCHEMA articles were tested. There must be way more going on behind the scenes then meets the eye. Using sys.* articles,...
Ross Bush's user avatar
  • 567
0 votes
0 answers
23 views

INSERT INTO SELECT FROM information_schema.processlist.info inside trigger bringing back INSERT query not original query

I have a trigger on a table that needs to get the Querytext that caused the trigger, Using a similar syntax as the following BEFORE INSERT ... INSERT INTO ... SELECT information_schema.processlist....
Anthony Sibley's user avatar
0 votes
0 answers
284 views

Remove not existing table from MySQL INFORMATION_SCHEMA

I removed (dropped) not used (in fact it was corrupted and that's why not used) table from a MySQL DB. I did it from MySQL Workbench. It was removed and it's not existing here. mysql> SELECT * FROM ...
Marek's user avatar
  • 13
0 votes
1 answer
150 views

column size differing when querying from all_tab_columns

We create a table as follows: CREATE TABLE "CHANGEWORKFLOWREQUEST" ( "DATA" CLOB CONSTRAINT "ChangeWorkflowRequest_data" CHECK ("DATA" IS JSON FORMAT JSON) ...
deostroll's user avatar
  • 189
0 votes
2 answers
466 views

How to display name and type of table columns in creation order?

I have created this tbl_cities using the code below: CREATE TABLE IF NOT EXISTS public.tbl_Cities (City_ID SERIAL NOT NULL, City_Name VARCHAR(100) NOT NULL, City_State_Region VARCHAR(100), ...
michal roesler's user avatar
0 votes
1 answer
971 views

MariaDB privileges for "SHOW DATABASES" vs "SELECT * FROM information_schema"

I have a user with an access to several databases. In MariaDB 10.6.11 I cannot use the following command to list all accessible databases: SHOW DATABASES; /* SQL Error (1227): Access denied; you need (...
oneee's user avatar
  • 23
0 votes
1 answer
24 views

I need advice for which database system to use

I have to create a database from scratch for the first time and I am not sure which system I should try. I will have specific IP addresses, IP ranges, domain names, host names which will be the key ...
thanley's user avatar
  • 11
0 votes
1 answer
36 views

How to Design Schema for Many To Many for a single Table

What is the right way to design schema for self Nested or many to many relationship for single Table Table:Products id name status Need to Match/save Products with related Products. ex: Related ...
Venkat Aadithan's user avatar
0 votes
1 answer
198 views

Unable to find variable : mysql-monitor_username in global_variables table in information_schema database in MySQL

I am trying to perform the initial configuration in proxysql and I found that while doing the Configure monitoring, I am unable to find the variable mysql-monitor_username in the global_variables ...
msbeast's user avatar
  • 21
-3 votes
1 answer
171 views

Fastest way to query column names from a table

I am looking for the fastest way to query the a list of columns from a given table in a database. I realize the INFORMATION_SCHEMA.columns view exists in Microsoft SQL Server. However, in PostgreSQL, ...
Daniel L. VanDenBosch's user avatar
0 votes
1 answer
3k views

MYSQL 8.0.2.8 Information_schema character set is incorrect, and I cannot find where to change it

I have a database installation where the collations look as so: As you can see, all the databases are utf8mb4, while the information schema is showing up as utf8mb3. This is causing me issues as I ...
M.Barbara's user avatar
1 vote
2 answers
1k views

MySQL information.schema.tables shows create_time later than update_time?

I've checked the documentation, but couldn't find anything that could explain this. mysql> SELECT * FROM information_schema.`TABLES` WHERE table_schema='myschema' AND TABLE_NAME ='mytable' \G; *****...
Duru Can Celasun's user avatar
1 vote
1 answer
3k views

How is the information_schema table in trino updated?

We use Trino (https://trino.io/) to connect to HDFS. I discovered that the data in the information_schema tables, for example: select * from information_schema.columns clz where clz....
raphael75's user avatar
  • 244
0 votes
1 answer
2k views

Can i join output of "Describe" queries with other tables in snowflake

I trying to use the Describe table in more complex way, thing like join with other tables , or other sql manipulation. On the other hand it's look that snowflake not accept the Describe table as ...
Eyal leshem's user avatar
3 votes
1 answer
5k views

What is faster: pg_catalog or information_schema?

I understand the high-level differences between pg_catalog and information_schema. But in many cases, a tool could choose to use any of them. And I'm wondering if there are performance reasons why we ...
Federico Razzoli's user avatar
0 votes
2 answers
221 views

Is there a way to examine all column definitions in MS-SQL?

I'm making a first program in MS-SQL. The idea is to make a proposal for a next tupple. One of the features is based on auto-increment (in case auto-increment is set, take the highest filled-in value, ...
Dominique's user avatar
  • 581
0 votes
2 answers
1k views

What can go wrong while querying INFORMATION_SCHEMA for schema names? [duplicate]

In this doc page on the use of the SQL standard INFORMATION_SCHEMA views, on the columns about schema names, the following warning is repeated each time: ** Important ** Do not use INFORMATION_SCHEMA ...
Mason Wheeler's user avatar
0 votes
0 answers
101 views

Information schema standard - where can I find more information?

Wikipedia says that information schema is an ANSI-standard and I found many implementations of it but I can't find a paper by ANSI that describes it. Can you help me to find out more about it?
0442A403's user avatar
0 votes
1 answer
425 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 ...
paranormaldist's user avatar
0 votes
0 answers
152 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 ...
user2834566's user avatar
1 vote
1 answer
6k 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 ...
slanden's user avatar
  • 143
0 votes
0 answers
204 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 ...
Marinos An's user avatar
0 votes
0 answers
179 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 ...
Nick Muise's user avatar
3 votes
1 answer
887 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 ...
iskandarblue's user avatar
0 votes
1 answer
3k 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 ...
J.D.'s user avatar
  • 38.3k
1 vote
1 answer
690 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 ...
Ivan Voras's user avatar
0 votes
1 answer
601 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 ...
febry's user avatar
  • 57
1 vote
0 answers
802 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 ...
Radu Murzea's user avatar
1 vote
0 answers
2k 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 ...
user avatar
1 vote
1 answer
624 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 = '...
Itération 122442's user avatar
0 votes
4 answers
3k 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 = '...
Rupesh Bhairat's user avatar
0 votes
1 answer
55 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(@...
E.Greg Kane's user avatar
0 votes
1 answer
268 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 ...
Daniel L. VanDenBosch's user avatar
1 vote
0 answers
31 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 ...
amaster's user avatar
  • 190
1 vote
1 answer
2k 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 ...
oorst's user avatar
  • 133
1 vote
1 answer
49 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: ...
Erdinc Ay's user avatar
  • 167
1 vote
3 answers
4k 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 ...
dolmen's user avatar
  • 135
2 votes
2 answers
868 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 ...
ali's user avatar
  • 21
1 vote
1 answer
847 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....
Elliott B's user avatar
  • 147
0 votes
1 answer
43 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 ...
MacMike's user avatar
2 votes
1 answer
2k 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 ...
Subrata Das's user avatar
3 votes
2 answers
20k 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 ...
Mitch Kent's user avatar
1 vote
2 answers
5k 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 ...
Racer SQL's user avatar
  • 7,416
1 vote
3 answers
255 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=...
joseph's user avatar
  • 113
1 vote
1 answer
1k 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 ...
mico's user avatar
  • 511
1 vote
1 answer
446 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 ...
IGGt's user avatar
  • 2,156
0 votes
2 answers
6k 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 ...
Warrior's user avatar
0 votes
1 answer
390 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 ...
Sybil's user avatar
  • 2,548