information schema : ODBC standard Views of RDBMS MetaData

learn more… | top users | synonyms

1
vote
1answer
51 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", ...
0
votes
1answer
38 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 ...
0
votes
1answer
46 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 ...
0
votes
1answer
54 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 ...
0
votes
0answers
24 views

Modify MySQL information_schema (specially create_time) on AWS RDS?

I am trying to modify the create_time of an entire database, tables, etc. I have found that information_schema tables stores the creation date of the tables on the column CREATE_TIME. Unfortunately ...
0
votes
1answer
51 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
31 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
46 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 ...
2
votes
4answers
970 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
14 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
89 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
64 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
1k 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.
1
vote
2answers
660 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, ...
4
votes
1answer
712 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 ...
2
votes
1answer
254 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.
0
votes
1answer
76 views

Query databases that have MYISAM tables that do not have only fulltext index tables

I am trying to query from the information_schema where the database (table_schema) has MYISAM tables but does not have only fulltext index tables. The statistics tables is very poorly designed for ...
0
votes
0answers
124 views

How can I make a copy of a table from information_schema?

I would like to extract meta information from information_schema like count of rows in a table and store the result table into "meta" database. However, while I can query tables in information_schema, ...
0
votes
0answers
34 views

Get reference count of reverse foreign key relations

Image I have a simple user table like this: ID|Name ------- 12|Foo I want to know: How many reverse foreign key relations point to the user with ID 12? I mean the number of rows which are a foreign ...
0
votes
2answers
25 views

Added a unique index, but cannot find it in TABLE_CONSTRAINTS

I'm running the following command in my DB: CREATE UNIQUE INDEX [UK_NAME1] ON [TABLE1] ([COLUMN1] ASC); Now, I'm running the following query: SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS ...
2
votes
1answer
229 views

Compare the structure of two MySQL Databases

I have two MySQL databases that are very similar to each other. How can I find out the differences in tables, and the differences in columns in each table? the databases are in different schema. ...
1
vote
1answer
135 views

Check for table lock in conditional statement [closed]

How can I make this work? IF( EXISTS (SHOW OPEN TABLES WHERE In_use < 2 and `Database` like 'MYDB' ) ) THEN -- DO SOMETHING END IF Context I want to run some sql statements inside a procedure, ...
1
vote
1answer
108 views

Finding out the exact size of the database after inserting records?

I have this query: SELECT table_schema "db.name" , Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema; Does this ...
1
vote
2answers
186 views

mysql information schema database last used datetime

I have a lot of tables in my database, almost 1,000. Most of them I am not using now. Is there any way to know from the information schema tables when I have last used my tables?
0
votes
1answer
2k views

Difference between character_maximum_length and character_octet_length

Maybe there is an answer for this somewhere but, I couldn't find any efficient answer on Google for this question, therefore, I will ask this here. I wanted to get some information about my Stored ...
1
vote
1answer
1k views

How performance schema in mysql 5.6 improve performance?

Since MySQL 5.5 performance_schema database has been added likewise information_schema. As we know that tables in information_schema contains statistical information like tables, plugins, partitions, ...
0
votes
2answers
207 views

Are these linked server queries susceptible to SQL injection?

I'm using a stored procedure to generate a create table script on the fly based on parameters passed into it. I am validating that the server exists, and then validating if the table exists in the ...
0
votes
1answer
47 views

How to relate to information_schema

Is it possible to create a foreign key that relates to information_schema? For an application implementation, I would like to store extra column information in another table, but I want to ensure ...
0
votes
1answer
90 views

What is the distinction between ALL_PROCEDURES and DBA_PROCEDURES, and other schema tables?

I recently failed to find a known procedure when searching ALL_PROCEDURES, and on a whim tried DBA_PROCEDURES: lo, there it is. The doc pages (ALL_; DBA_) both say "...lists all functions and ...
2
votes
3answers
1k views

Whats the difference between information_schema.user_privileges and mysql.user?

I have the notion that privileges set in mysql.user should reflect in information_schema.user_privileges but when I try to alter some privileges in mysql.user and queried ...
1
vote
1answer
1k views

Need help to understand how information_schema.TABLES get updated

I created large innodb table mysql> select count(*) from big_data; +----------+ | count(*) | +----------+ | 23822336 | +----------+ checked the stats in informaion_schema.TABLES mysql> ...
0
votes
1answer
438 views

copy table comments from one MySQL database to another

I wrote column comments in MySQL database replica made for testing. Now I need to copy these comments to production database. Since comments are stored in table COLUMNS of information_schema database, ...
0
votes
1answer
231 views

CentOS and Ubuntu differences with information_schema

I have a Vagrant VM running CentOS 6.5. My localhost is Ubuntu 12.04, both running MySQL. I have the exact same database on both (structured exactly the same, different testing seed data in both). ...
0
votes
0answers
55 views

Table Field to Reference Field in Other Tables

Suppose I want to map a set of input values to database table fields E.g. employeeFirstName --> emp.fName employeeLastName --> emp.lName ... --> ... In theory I can create a ...
1
vote
1answer
957 views

Use result from table_schema query as <table> in select * from <table>

Is it possible in PostgreSQL to use the result of the query below as table name in a SELECT * FROM . SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'seam_event_%' ORDER BY ...
2
votes
1answer
2k views

How to resolve red invalid icon for Informatica mapping

I'm having a difficult time resolving a red "invalid" icon within Informatica. I've done all possible scenarios for checking in / checking out this object (mapping). It currently does not have any ...
5
votes
2answers
3k views

Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data?

Is there a quick way to find all columns in SQL Server 2008 R2 that are encrypted/have encrypted data? I need to nullify the data in all encrypted columns in a development server (according to our ...
-1
votes
2answers
515 views

Mysql - Change name of Information_schema [closed]

I want to change the "Information_schema" database name to "some_thing_else". Is this possible? If so, how can I do so? As a developer knows about structure and a hacker also. How can we protect ...
1
vote
1answer
127 views

Does this query always correctly retrieve the types of array parameters

The following query seems to work, but I am not sure that it is the best/always correct way: select parameter_name, CASE WHEN data_type='ARRAY' THEN SUBSTRING(udt_name, 2)||'[]' ELSE ...
3
votes
2answers
451 views

Adding an index to a system catalog in Postgres

I'm having a situation very similar to the one described here: I've got a SaaS situation where I'm using 1000+ schemas in a single database (each schema contains the same tables, just different ...
4
votes
1answer
3k views

PostgreSQL: How to list all stored functions that access specific table

Introduction: PostgreSQL database with several hundred of stored functions, including obsolete, not used etc. Problem I need to find out all the stored functions that have any relationship to the ...
-3
votes
3answers
101 views

Listing databases that are not in use

I want to show databases that are not used. For that I tried this query, but the result set is empty: mysql> SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME NOT IN ...
4
votes
2answers
10k views

Get the Time of Last update of a column

This command gives the date of the last update for a table SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = 'MyDB' AND TABLE_NAME = 'MyTable' But I want to find the time of ...
1
vote
1answer
650 views

Is there a way to find the least recently used tables in a schema?

Is there a way to find the least recently used tables in a MySQL schema? Besides going into data directories? I was hoping there was a metadata or status trick-- but Update_Time in STATUS and ...
3
votes
3answers
7k views

How can I search the full text of a stored procedure for a value?

I use the following script to search the text of all stored procedures when I want to find specific values. SELECT ROUTINE_NAME, ROUTINE_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION ...
1
vote
1answer
346 views

mysqldump, preserve update_time and modify_time attributes as listed by show table status from

Does mysqldump preserve the create_time and update_time attributes that are output by show table status from? If not, is there an option that does this? From the manual it looks like mysqldump ...
1
vote
2answers
1k views

list tables that are missing primary/foreign key in postgresql database

I've seen a ton of queries to list primary and foreign keys but how can I query for tables missing primary and foreign keys? PostgreSQL 8.4 and 9.x versions
4
votes
1answer
4k views

query to identify all data types used in postgresql database tables

I don't want to know all data types, just all data types used in my database. Can this information be queried? PostgreSQL 8.4 and 9.x versions I currently need to know all data types for over 200 ...
2
votes
1answer
267 views

Number of virtual tables that specified user is able to write queries in PostgreSQL

I have one question about PostgreSQL that I can't understand. As it seems so far, it needs some of bruteforce in tables. Well, I did that lots of time and I found nothing. So, firstly I will cover my ...
2
votes
2answers
426 views

Does MySQL have information_schema.VIEW_COLUMN_USAGE or an equivalent?

SQL Server has a an information_schema view, 'VIEW_COLUMN_USAGE', which you can use to determine the actual/base tables that the columns in a view reside in. I'm looking for the equivalent in MySQL - ...