Stack Overflow
Stack Overflow

Questions tagged [information-schema]

The information schema is a set of views providing metadata for objects in relational databases: tables, views, columns, stored procedures, accounts etc.

The information schema is a set of read-only views defined by an ANSI standard and implemented by most popular RDBMS (with the notable exception of Oracle).

Wikipedia has links to the documentation for the most popular RDBMS.

549 questions
1025
votes
11 answers

How can I find all the tables in MySQL with specific column names in them?

I have 2-3 different column names that I want to look up in the entire database and list out all tables which have those columns. Is there any easy script?
Jobi Joy
  • 49,102
  • 20
  • 108
  • 119
335
votes
15 answers

How to check if a Constraint exists in Sql server?

I have this sql: ALTER TABLE dbo.ChannelPlayerSkins DROP CONSTRAINT FK_ChannelPlayerSkins_Channels but apparently, on some other databases we use, the constraint has a different name. How do I check if there's a constraint with the name…
Lieven Cardoen
  • 25,140
  • 52
  • 153
  • 244
278
votes
9 answers

List all tables in postgresql information_schema

What is the best way to list all of the tables within PostgreSQL's information_schema? To clarify: I am working with an empty DB (I have not added any of my own tables), but I want to see every table in the information_schema structure.
littleK
  • 19,521
  • 30
  • 128
  • 188
277
votes
7 answers

PostgreSQL query to list all table names?

Is there any query available to list all tables in my Postgres DB. I tried out one query like: SELECT table_name FROM information_schema.tables WHERE table_schema='public' But this query returns views also. How can i get only…
jobi88
  • 3,865
  • 8
  • 21
  • 15
224
votes
3 answers

How to check if a table exists in a given schema

Postgres 8.4 and greater databases contain common tables in public schema and company specific tables in company schema. company schema names always start with 'company' and end with the company number. So there may be schemas…
Andrus
  • 26,339
  • 60
  • 204
  • 378
137
votes
16 answers

How do I find a default constraint using INFORMATION_SCHEMA?

I'm trying to test if a given default constraint exists. I don't want to use the sysobjects table, but the more standard INFORMATION_SCHEMA. I've used this to check for tables and primary key constraints before, but I don't see default constraints…
WildJoe
  • 5,740
  • 3
  • 26
  • 30
78
votes
6 answers

How can I test if a column exists in a table using an SQL statement

Is there a simple alternative in PostgreSQL to this statement produced in Oracle? select table_name from user_tab_columns where table_name = myTable and column_name = myColumn; I am then testing whether the query returns anything so as to prove the…
CSharpened
  • 11,674
  • 14
  • 52
  • 86
65
votes
7 answers

SQLite Schema Information Metadata

I need to get column names and their tables in a SQLite database. What I need is a resultset with 2 columns: table_name | column_name. In MySQL, I'm able to get this information with a SQL query on database INFORMATION_SCHEMA. However the SQLite…
ahmet alp balkan
  • 42,679
  • 38
  • 138
  • 214
58
votes
3 answers

Finding columns that are NOT NULL in PostgreSQL

I had an assignment for each table to count nullable columns. Easy: SELECT table_name, count(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE is_nullable='NO' GROUP BY table_name; Now I have to modify this to count "columns that have property "NOT…
Allan David Munja
  • 581
  • 1
  • 4
  • 3
50
votes
9 answers

What is the most portable way to check whether a trigger exists in SQL Server?

I'm looking for the most portable method to check for existence of a trigger in MS SQL Server. It needs to work on at least SQL Server 2000, 2005 and preferably 2008. The information does not appear to be in INFORMATION_SCHEMA, but if it is in there…
Blorgbeard
  • 101,031
  • 48
  • 228
  • 272
48
votes
4 answers

SQL Server: Howto get foreign key reference from information_schema?

In SQL Server, how can I get the referenced table + column name from a foreign key? Note: Not the table/column where the key is in, but the key it refers to. Example: When the key [FA_MDT_ID] in table [T_ALV_Ref_FilterDisplay]. refers to…
Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442
33
votes
2 answers

Behaviour of NOT LIKE with NULL values

I want to fetch all columns of a table except of columns of type serial. The closest query to this problem I was able to come up with this one: SELECT column_name FROM information_schema.columns WHERE table_name = 'table1' AND column_default NOT…
kushi
  • 389
  • 1
  • 5
  • 11
27
votes
1 answer

What does the information_schema database represent?

I have one database in mysql. But when i log into phpMyAdmin , it shows another database called information_schema. Is that database always present with one database? I mean to say is there a copy of information_schema for every database present in…
John
24
votes
2 answers

MySQL disable all triggers

For test correctness of query I need disable all triggers in db. I see that in information_schema exists table TRIGGERS. Is possible temporarily disable all triggers using this table? E.g. like: update TRIGGERS set TRIGGERS_SCHEMA='myschema_new'…
user810430
  • 11,181
  • 15
  • 38
  • 43
24
votes
2 answers

Can DESCRIBE syntax be embedded in SELECT statement?

In MySQL, the syntax DESCRIBE can show a table's structure, but it cannot be embedded to normal statement; is there some tricky way to do it? For example, this shows the table1 structure, returned as a table (but SQL statement does not think…
JimZ
  • 1,182
  • 1
  • 13
  • 30
1
2 3
…
36 37