Questions tagged [schema]

A schema of a database system is its structure described in a formal language supported by the database management system (DBMS) and refers to the organization of data to create a blueprint of how a database will be constructed (divided into database tables).

1034 questions
384
votes
4 answers

How do I list all schemas in PostgreSQL?

When using PostgreSQL v9.1, how do I list all of the schemas using SQL? I was expecting something along the lines of: SELECT something FROM pg_blah;
Stéphane
  • 4,945
  • 4
  • 17
  • 12
102
votes
8 answers

Difference between database vs user vs schema

I'm really confused with the terms database, user and schema. Can anyone explain how they are different from each other (if they are)? If they are same then, what are the similarities between them? How do we use them? And how do we create them?
Ravi
  • 1,485
  • 4
  • 15
  • 20
73
votes
5 answers

Is there a name for this database schema of key values?

We process a routine data feed from a client who just refactored their database from a form that seems familiar (one row per entity, one column per attribute) to one that seems unfamiliar to me (one row per entity per attribute): Before: one column…
prototype
  • 843
  • 1
  • 7
  • 6
68
votes
5 answers

How can a group track database schema changes?

What version control methodologies help teams of people track database schema changes?
Toby
  • 1,128
  • 2
  • 12
  • 12
61
votes
6 answers

ERROR: no schema has been selected to create in

I am working on an amazon RDS postgresql database where I know there had been some issue with the public schema (maybe it was dropped). But apparently the schema exists, and anyway the problem is not solved. Here is a sample session with a newly…
Emanuele Paolini
  • 713
  • 1
  • 5
  • 7
57
votes
1 answer

Best practices for schema changes and data migrations to a live database without downtime?

How do you make schema changes to a live database without downtime? For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all associated with specific users. If I wanted to move the…
Dan Leary
  • 673
  • 1
  • 6
  • 5
41
votes
5 answers

Backup only one schema and restore it on another server

I have a database named "A" which has two schemas "B" and "C". I want to backup and restore Schema "B" (including data) on a different server. Not sure how to do this as I am new to Postgres. Do I have to create a new database on the new sever as of…
user30422
  • 511
  • 1
  • 4
  • 3
35
votes
4 answers

Should dbo schema be avoided?

When it comes to the dbo schema: Is it a best practice to avoid using the dbo schema when creating database objects? Why should the dbo schema be avoided or should it? Which database user should own the dbo schema?
jrara
  • 5,253
  • 19
  • 53
  • 65
35
votes
7 answers

Copy complete structure of a table

Using some methods, when you create a copy of a table you lose indexes, PK, FK, etc. For example in SQL Server I can say: select * into dbo.table2 from dbo.table1; This is just a simple copy of the table; all of the indexes / constraints are…
jcho360
  • 1,979
  • 7
  • 23
  • 31
34
votes
18 answers

How can I compare the schema of two databases?

Is there a way to find the differences in two SQL Server databases (schema only). One is local and the second is at a customer's site. We are experiencing problems with crystal reports running some reports and some code not executing and it would…
user1571430
  • 441
  • 1
  • 4
  • 3
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
29
votes
3 answers

What are some best practices for using schemas in SQL Server?

I understand the capabilities of SQL Server Schemas, but what are the best practices? Sure they give another layer of security, and provide logical grouping of database objects within the database, but what is typical out there? I often, in my…
anon
29
votes
3 answers

How to transfer data using expdp and impdp commands?

I'm an Oracle noob, and my intention is to transfer all data and metadata from one schema to another schema within an Oracle database. I'm planning to use datapump's expdp and impdp commands. I have questions regarding this: Can I create a target…
jrara
  • 5,253
  • 19
  • 53
  • 65
28
votes
2 answers

Difference between Owned Schemas and Role Membership for Microsoft SQL Server

Are there differences between Owned Schemas and Role Members? From my understanding, the Role Members are compulsory - user need to have at least one of the check box under Role Members checked. However, the Owned Schemas is not compulsory. It…
Jack
  • 2,429
  • 14
  • 35
  • 42
25
votes
6 answers

PostgreSQL: Creation date of a table

I've recently finished a project, during which many DB tables were created. Most of these tables contain temporary garbage, and I am looking for a simple way to list all these tables. Is there a way to list all DB tables sorted according to their…
Adam Matan
  • 11,019
  • 29
  • 79
  • 94
1
2 3
68 69