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).
Questions tagged [schema]
1011 questions
372
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,805
- 4
- 17
- 12
101
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,475
- 4
- 15
- 20
72
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
- 833
- 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
56
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
- 663
- 1
- 6
- 5
56
votes
5 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
- 663
- 1
- 5
- 7
38
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
- 481
- 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,223
- 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,978
- 7
- 23
- 31
33
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
- 431
- 1
- 4
- 3
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,223
- 19
- 53
- 65
28
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
- 727
- 3
- 7
- 10
28
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
27
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,379
- 14
- 33
- 42
24
votes
5 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
- 10,609
- 28
- 76
- 94