Skip to main content
43 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-...
Dustin's user avatar
  • 531
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, ...
Evan Carroll's user avatar
  • 63.6k
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" ...
Evan Carroll's user avatar
  • 63.6k
12 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 ...
naian's user avatar
  • 121
10 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, '{...
oᴉɹǝɥɔ's user avatar
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 ...
Aaron Bertrand's user avatar
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%';
joao victor silva de oliveira's user avatar
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 ...
Erwin Brandstetter's user avatar
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....
Evan Carroll's user avatar
  • 63.6k
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]; ...
Jesse's user avatar
  • 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 ...
Evan Carroll's user avatar
  • 63.6k
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 ...
Daniel Vérité's user avatar
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 ...
Balazs Papp's user avatar
  • 40.8k
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 ...
ypercubeᵀᴹ's user avatar
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 ...
Aaron Bertrand's user avatar
3 votes

GRANT SELECT ON SEQUENCE succeeds, but to no effect?

The manual: The view role_usage_grants identifies USAGE privileges granted on various kinds of objects where the grantor or grantee is a currently enabled role. Nothing additional will show up ...
Erwin Brandstetter's user avatar
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) ...
mustaccio's user avatar
  • 26.2k
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 ...
Tibor Karaszi's user avatar
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 ...
J.D.'s user avatar
  • 38k
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 ...
Erwin Brandstetter's user avatar
2 votes

How can I list all tables without a primary key?

I had issues with @a_horse_with_no_name's answer, which missed tables for me. grayhemp's returned views and gave me completely wrong results in our production environment for some reason. I wrote this ...
MaxGabriel's user avatar
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> ...
greenlitmysql's user avatar
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 ...
André Christoffer Andersen's user avatar
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 ...
Sybil's user avatar
  • 2,518
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 ...
RolandoMySQLDBA's user avatar
2 votes
Accepted

What permissions are required to return rows from information_schema.schemata?

The user needs to be the owner of a schema to get any rows from information_schema.schemata alter schema $schemaName owner to $user; Or the user needs to be a member of a group that owns the schema ...
Neil McGuigan's user avatar
2 votes
Accepted

Is there a way to get SQL Server capcity constraints dynamically

No there's nothing built in that is exposed to us. Your app would need to maintain some sort of configuration table itself. You can't even try and parse it out of sys.messages as it isn't hardcoded ...
Martin Smith's user avatar
  • 84.9k
2 votes
Accepted

How do I navigate PostgreSQL documentation to find a list of all functions that work on a specific type?

There are a few ways, but let's try and address all three of them.. man pages There are no man pages that will address these questions. There is a lot of PostgreSQL documentation but currently ...
Evan Carroll's user avatar
  • 63.6k

Only top scored, non community-wiki answers of a minimum length are eligible