42
votes
Query the definition of a materialized view in Postgres
Looks like 9.3 and up you can do:
select * from pg_matviews;
select * from pg_matviews where matviewname = 'view_name';
More info found here: https://stackoverflow.com/questions/29297296/postgres-...
- 521
14
votes
List all columns for a specified table
psql on PostgreSQL 11+
If you're looking for the column types on a query, you can use psql's \gdesc
SELECT
NULL AS zero,
1 AS one,
2.0 AS two,
'three' AS three,
$1 AS four,
...
- 62.1k
13
votes
Accepted
Why does information_schema have "YES" and "NO" character strings rather than booleans?
INFORMATION_SCHEMA
Using psql you can see the schema with \d information_schema.columns, or you can look it up in the docs.
View "information_schema.columns"
...
- 62.1k
9
votes
Accepted
INFORMATION_SCHEMA.VIEW_TABLE_USAGE only shows tables from the same catalog
Yes, stop using INFORMATION_SCHEMA for various reasons, including one I didn't mention in that post (yet), which is the fact that they don't handle cross-database dependencies.
Use the catalog views ...
- 180k
9
votes
Calculate row size and max row size for a table
Here are my two cents using SQL and querying the INFORMATION_SCHEMA.COLUMNS table, based on the MySQL documentation 'Data type storage requirements'.
Note: this is not be entirely accurate, as ENUM ...
- 91
9
votes
List all columns for a specified table
PostgreSQL only
This is somewhat hokey but could be a contender if you are looking for the shortest possible SQL:
SELECT json_object_keys(to_json(json_populate_record(NULL::schema_name.table_name, '{...
- 471
9
votes
Accepted
Why shouldn't INFORMATION_SCHEMA views be used to determine the schema of an object?
Yes, the statement there is inaccurate. I talk about this in this blog post (and complained about it in this ancient Connect item), where I implore people to use the sys catalog views over ...
- 180k
6
votes
PostgreSQL: How to list all stored functions that access specific table
This query is pretty easy to use:
SELECT proname, proargnames, prosrc FROM pg_proc WHERE prosrc ILIKE '%Text to search%';
6
votes
Accepted
What is faster: pg_catalog or information_schema?
Typically, pg_catalog is faster.
The true source of information in Postgres are the catalog tables in pg_catalog. Views in the information_schema are based on those. Sometimes those views are rather ...
- 172k
5
votes
Accepted
How do I query INFORMATION_SCHEMA.COLUMNS on Amazon RDS?
PG_TABLE_DEF
Amazon considers the internal functions that INFORMATION_SCHEMA.COLUMNS is using Leader-Node Only functions. Rather than being sensible and redefining the standardized INFORMATION_SCHEMA....
- 62.1k
4
votes
Accepted
Calculate row size and max row size for a table
After much of thinking and researching found one answer which really helped in achieving what was required. It is a perl script and reference link is
http://dev.mysql.com/doc/refman/5.6/en/storage-...
- 1,440
4
votes
Accepted
Does INFORMATION_SCHEMA always contain up-to-date metadata?
Yes, the data is up-to-date. But don't read too much into the numbers.
InnoDB, especially, preallocates space for future rows. So, it is quite possible to look at the exact size of a table, insert ...
- 76.9k
4
votes
Deny access to information schema in SQL Server
I am unsure of when this trick became available - since no one has mentioned it - but it appears that it works at least since SQL Server 2008.
DENY VIEW DEFINITION to [database-role / database-user];
...
- 151
4
votes
Accepted
postgres system catalog query columns from table
Just select from information_schema.columns instead.
SELECT table_catalog, table_schema, table_name, data_type
FROM information_schema.tables
WHERE table_schema='{schema}';
FOR GLORY
For whatever ...
- 62.1k
4
votes
Accepted
GRANT SELECT ON SEQUENCE succeeds, but to no effect?
GRANT'ing SELECT (or USAGE) on the sequence is not sufficient if it's contained in a schema for which the user has no permission. I believe that's the case because your schema named public is not ...
- 30.8k
4
votes
column size differing when querying from all_tab_columns
Works as designed, the problem is the expectation, not the result from dictionary.
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/ALL_TAB_COLS.html
DATA_LENGTH | NUMBER | NOT ...
- 40.3k
3
votes
count null values of table from information_schema tables
No, you cannot use information schema for this. Information_schema.TABLES only stores an (approximate) count of the number of rows in a table. Not counts of values or nulls.
You should have an index ...
- 97.3k
3
votes
Accepted
INFORMATION_SCHEMA.GLOBAL_VARIABLES alternative in 5.7? More info about show_compatibility_56
You need to enable the PERFORMANCE_SCHEMA.
See the documentation for details:
The Performance Schema is disabled by default. To enable it, start the
server with the performance_schema variable ...
- 31.6k
3
votes
Accepted
Is it possible to concatenate query results to form a new query?
Unless you are explicitly trying to write queries that work in multiple engines, I recommend sticking to the catalog views in the sys schema rather than INFORMATION_SCHEMA (see this and this). You'll ...
- 180k
3
votes
Accepted
Why is the value of NUMERIC_SCALE column in INFORMATION_SCHEMA.COLUMNS table for DATA_TYPE='double' NULL?
Floating point numbers are exactly that -- there's no fixed position for the decimal separator (point), it can be anywhere, and specifying scale (i.e. the number of digits after the decimal separator) ...
- 25k
3
votes
Is there a way to examine all column definitions in MS-SQL?
The INFORMATION_SCHEMA columns are meant to be ANSI/ISO SQL compliant. In practice this means that they tend to be the lowest common denominator. I.e., stuff that isn't in the SQL standard or perhaps ...
- 17k
3
votes
Accepted
How to Design Schema for Many To Many for a single Table
You would create a second table called RelatedProducts like so (pseudo-code since you didn't mention a specific database system):
CREATE TABLE RelatedProducts
(
ProductId INT,
RelatedProductId ...
- 35k
3
votes
Accepted
How to display name and type of table columns in creation order?
In Postgres, catalog tables are the true source of information. For this case in particular: pg_attribute. The column attnum represents the physical order (= logical order) of columns:
SELECT attname ...
- 172k
2
votes
INFORMATION_SCHEMA.GLOBAL_VARIABLES alternative in 5.7? More info about show_compatibility_56
Wrong answer. Performance schema has nothing to do with information schema.
The reason you get an error related to GLOBAL_VARIABLES which is in information is due to compatibility with 5.6 ;
mysql> ...
- 464
2
votes
Accepted
Working with PostgreSQL information_schema and tables (with same names, in different schemas) whose names start with a number
@ypercube's solution (in the comments above):
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'crs_1973' AND
table_name = '2015_04_21'
ORDER BY ordinal_position;
...
- 897
2
votes
Query the definition of a materialized view in Postgres
The drawback with the other answers here is that you just get the SQL definition, while in most cases you are interested in the actual columns, and being able to manipulate them as text. The following ...
2
votes
how to update information_schema.tables in mysql
Information_Schema are views and read-only. Yo can not update meta data about tables, only by modifying actual table with ALTER TABLE. If you do an ALTER TABLE you will have current timestamp in meta ...
- 2,448
2
votes
What happens to mysql information_schema after switching to innodb_file_per_table?
The information_schema database is an all memory database made up of temporary tables. It is not stored in any of the InnoDB plumbing.
I wrote about this 5 years ago : How is INFORMATION_SCHEMA ...
- 181k
Only top scored, non community-wiki answers of a minimum length are eligible