information schema : ODBC standard Views of RDBMS MetaData

learn more… | top users | synonyms

1
vote
0answers
14 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 ...
0
votes
1answer
37 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 ...
0
votes
2answers
40 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 ...
1
vote
1answer
148 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
1answer
32 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 ...
5
votes
1answer
44 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 ...
4
votes
4answers
8k 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
votes
1answer
35 views

I run the script and it freezes me but varchar(max) and nvarchar(max) -1 always the ones with throws. How max turns -1 [closed]

ALTER FUNCTION [dbo].[get_logtable_script] (@table_name varchar(max)) RETURNS varchar(max) AS BEGIN declare @sql varchar(5000) declare @column_name varchar(max), @data_type varchar(max), ...
7
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 ...
5
votes
1answer
2k 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....
0
votes
0answers
20 views

MySql event scheduler skips events for several minutes: why?

I'm trying to diagnose some troubles on a couple of installations of a webapp that occasionally stop responding (Tomcat 7 and MySQL 5.6). Since the hangs only happen on one or two of these ...
1
vote
0answers
55 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 ...
0
votes
1answer
30 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', '...
1
vote
0answers
58 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 ...
68
votes
3answers
139k views

How do I list all columns for a specified table

I'm looking for a precise piece of information in a database which I have no knowledge about. It's a 3rd party product, they are slow on answering some questions, and I know the data is lying inside ...
1
vote
1answer
81 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
49 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
259 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
74 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 ...
4
votes
1answer
4k 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 ...
0
votes
0answers
62 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 ...
1
vote
1answer
113 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
33 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 ...
3
votes
4answers
3k 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
126 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
98 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 ...
2
votes
2answers
2k 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
1answer
3k 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 ...
1
vote
2answers
1k 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, ...
2
votes
1answer
514 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
90 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
158 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, ...
2
votes
2answers
11k views

How can I list all user-owned functions?

Is there a way to quickly list all the current user-owned functions in Oracle? Something along the same lines as this, which lists the current user's tables select * from user_tables;
0
votes
0answers
39 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
27 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 ...
5
votes
3answers
15k views

Understanding Oracle's ALL_TAB_COLUMNS

I'm new to Oracle and database administration in general. As context, I want to create a Java class that will give me the information I would get using DESC SOME_TABLE. I cannot find any specific ...
2
votes
1answer
260 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. It'...
1
vote
1answer
160 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
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> ...
4
votes
2answers
12k 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 ...
10
votes
2answers
9k views

Deny access to information schema in SQL Server

I am looking for the best way to disable access to the sys.tables / Information Schema for a user / group in SQL Server. I found this thread from 2008 It shows a way how to deny access on [sys].[...
1
vote
2answers
235 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 ...
0
votes
2answers
245 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 ...
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
1answer
49 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
111 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 information_schema....