information schema : ODBC standard Views of RDBMS MetaData
3
votes
2answers
79 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
0answers
30 views
information_schema.table_constraints doesn't exist error in MySQL
I'm new to this and Googling didn't help me with this one. I'm trying to check whether a foreign key constraint already exists like this:
$sql = 'SELECT `constraint_name`, `table_name` FROM ...
0
votes
1answer
98 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
126 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
2
votes
1answer
96 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
95 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
117 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
107 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 ...
3
votes
1answer
1k 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
117 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
330 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 ...
2
votes
3answers
2k 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
284 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
213 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
....
...
1
vote
1answer
221 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
940 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
190 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
1k 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
242 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
705 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 ...
3
votes
2answers
824 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 ...
8
votes
1answer
4k 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 ...
