Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [information-schema]

information schema : ODBC standard Views of RDBMS MetaData

The information schema for a Database consists of MetaData objects that define the following:

  • Tables
  • Fields or Columns
  • Indexes
  • Relationships
  • Procedures
  • Functions
  • Triggers
  • Packages
  • Types
  • Sequences
  • Views

Depending on the RDBMS, the information schema could have more object classes for holding XML schemas, synonyms, materialized views, events, and more.

Oracle (preferably known as Schema Objects), SQL Server, PostgreSQL, MySQL and other RDBMS's feature these objects either as all-memory entities or as physically instantiated files.

143 questions
487
votes
5 answers

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. The database is on a separate machine, but I can log into it, and launch a psql command line, with administrator rights. It's a third-party product, and…
Stephane Rolland
  • 7,903
  • 8
  • 29
  • 40
32
votes
4 answers

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.views WHERE table_name = 'some_view'; which…
Sean the Bean
  • 570
  • 1
  • 4
  • 11
29
votes
7 answers

Query to compare the structure of two tables in MySQL

To automate the backup process of one of my MySQL databases, I would like to compare the structure of two tables (current version vs old version). Can you think of a query that can compare two tables? Here are some example tables that you can…
sjdh
  • 747
  • 3
  • 8
  • 10
18
votes
2 answers

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 table X - as I want to change the table structure.…
Sergey Kudriavtsev
  • 487
  • 2
  • 5
  • 12
17
votes
6 answers

Calculate row size and max row size for a table

Is there any way of calculating the number of bytes occupied by the table? I know that you can get some information from information_schema.tables but that information is not accurate enough. What actually required is the number of bytes according…
Nawaz Sohail
  • 1,400
  • 3
  • 9
  • 25
15
votes
1 answer

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 installation I see two databases: mysql and…
ivotron
  • 533
  • 3
  • 5
  • 11
14
votes
3 answers

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].[something] like so: DENY SELECT ON [sys].[columns]…
SwissCoder
  • 359
  • 1
  • 3
  • 11
13
votes
4 answers

How can I list all tables without a primary key?

I've seen a ton of queries to list primary and foreign keys but how can I query for tables missing a primary key?
Phill Pafford
  • 1,305
  • 5
  • 18
  • 25
11
votes
4 answers

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 business rules). I know most of the columns because…
efesar
  • 1,042
  • 6
  • 12
10
votes
2 answers

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 last update of a particular column of a table. I…
dardar.moh
  • 275
  • 1
  • 3
  • 12
10
votes
1 answer

Why shouldn't INFORMATION_SCHEMA views be used to determine the schema of an object?

According to MS-DOCS about System information schema views, the schema columns definition have a warning note that says: ** Important ** Do not use INFORMATION_SCHEMA views to determine the schema of an object. The only reliable way to find the…
McNets
  • 23,199
  • 9
  • 46
  • 84
8
votes
1 answer

Display only table names with psql

How can I get the table names without a header or footer or anything else from a specified Postgres database with psql? That is, if I have tables called "table1" and "table2" then the output will be: table1 table2 and only that. I've been trying…
seumasmac
  • 181
  • 1
  • 1
  • 5
8
votes
2 answers

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 purpose or something that I'm missing? mysql> SHOW…
randomx
  • 3,914
  • 4
  • 29
  • 43
7
votes
1 answer

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 Procedure parameters dynamically, so I wrote this…
Misha Zaslavsky
  • 439
  • 2
  • 8
  • 15
7
votes
1 answer

Why does information_schema have "YES" and "NO" character strings rather than booleans?

I was taken by surprise to find that the is_nullable column for this query is typed as character varying (3) rather than boolean: select column_name, data_type, is_nullable from information_schema.columns where table_schema =…
devuxer
  • 181
  • 5
1
2 3
…
9 10