information schema : ODBC standard Views of RDBMS MetaData

learn more… | top users | synonyms

1
vote
2answers
34 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
204 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
1answer
232 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
113 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
36 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
38 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 ...
1
vote
3answers
236 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 ...
0
votes
1answer
253 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
0answers
353 views

MySQL Update Default Value of Column in Multiple Tables From An Inner Select Statement

I posted this question on stack overflow, but his might be a better place for it. Reference: ...
0
votes
1answer
264 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
116 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
43 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
401 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 ...
0
votes
1answer
385 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 ...
4
votes
2answers
1k 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
291 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
81 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 ...
2
votes
2answers
250 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 ...
2
votes
1answer
1k 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
87 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 ...
3
votes
2answers
4k 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
329 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
4k 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 ...
0
votes
1answer
251 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
587 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
3
votes
1answer
2k 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
203 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
326 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 - ...
2
votes
2answers
705 views

Does MySQL 5.5 support shared database with separate schema?

I am developing a SaaS based product using MySQL 5.5 as the backend database. I want to keep shared database for all the tenants, but separate a database schema of each tenant. There would be a single ...
4
votes
2answers
4k 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 ...
2
votes
1answer
286 views

Schema for 1 <-> 0..1(+), many related but only one primary

Using tools available in SQL Server 2008 and Entity Framework, what is a solid way to represent a one-to-many relationship, where there is an underlying unique relation that I need to enforce? My ...
3
votes
1answer
1k views

How do I merge two XML variables in SQL Server (2005/2008)?

Forward: I've already read this StackOverflow thread and it does not address the depth retention I need. I want to create an XML document that contains the data definition objects for a single ...
21
votes
3answers
37k 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 ...
2
votes
3answers
8k 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 ...
4
votes
2answers
2k views

Using the correct database when calling a system stored procedure in SQL Server 2008

I think there's something I'm missing when it comes to which database is being used when running a custom system stored procedure. I have the following in my stored procedure (edited for brevity): ...
3
votes
3answers
443 views

How can I select using the same fields from an undetermined number of tables using MySQL?

I'm dealing with a MySQL database where I have an undetermined amount of identically structured tables that look like this: foo_reference1 foo_reference2 foo_referencea foo_referenceb .... ...
2
votes
1answer
685 views

SQL query to INFORMATION_SCHEMA.TABLES to only return those I have select access to

I currently have a query in SQL 2008 that can return me a list of tables for a given database, provided I have View Definition access to them. select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, ...
1
vote
1answer
2k views

“show create table” output with out “show”?

What I'd like to be able to get the SQL statement by 'show create table' as something I could do something with along the lines of insert into mytable (myTextColumn) (show create table blah) I've ...
1
vote
1answer
299 views

Why are these queries slow in some environments and not others? Can we fix their performance?

The following stored procedures in a MySQL 5.x environment: DELIMITER // CREATE PROCEDURE sp_belongs_to (IN schemaName VARCHAR(100), IN tableName VARCHAR(100)) BEGIN SELECT TABLE_NAME as ...
2
votes
2answers
5k 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;
1
vote
1answer
380 views

schema shows through standard client connection but not through ODBC connection?

Hoping you can help! The Gist When I log into an Oracle 11g DB using SQL Developer or DBVisualizer, I can see schemas and execute queries against them. However, when I use the same credentials to ...
1
vote
2answers
2k views

Is the mapping between PostgreSQL system tables and information_schema views documented?

If you connect pgadminIII to a PostgreSQL 9.x server, you'll find two catalogs exposed in the user interface: ANSI (information schema), and PostgreSQL (pg_catalog). I'm looking for documentation ...
5
votes
2answers
2k views

What is the point of the TABLE_CATALOG column in INFORMATION_SCHEMA.TABLES?

In MySQL's table INFORMATION_SCHEMA.TABLES, there's a column named 'TABLE_CATALOG'. The documentation is sparse on this column and I'm wondering what the heck is the purpose of this? Any killer-app ...
10
votes
1answer
9k views

How is INFORMATION_SCHEMA implemented in MySQL?

The INFORMATION_SCHEMA is, in theory, a set of views specified in the SQL standard that allow the user to inspect the system's metadata. How is it this implemented in MySQL? When I connect to a fresh ...