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]
1069 questions
449
votes
5 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
- 5,745
- 5
- 20
- 13
110
votes
6 answers
Difference between database vs user vs schema
What is the difference (if any) between a database, a user, and a schema?
Ravi
- 1,565
- 4
- 15
- 20
74
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
- 853
- 1
- 7
- 6
71
votes
8 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
- 813
- 1
- 6
- 7
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
59
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
- 693
- 1
- 6
- 5
56
votes
3 answers
The database principal owns a schema in the database, and cannot be dropped message
I am trying to delete a principal from the database but can't because it owns a schema. When I go to edit the user, however, the box to uncheck schema is blue and unremovable.
How can I remove the principal from these schemas?
rsteckly
- 921
- 1
- 7
- 13
49
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
- 591
- 1
- 4
- 3
36
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
- 461
- 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,393
- 20
- 58
- 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
- 2,009
- 8
- 24
- 31
31
votes
9 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
- 767
- 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
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,557
- 14
- 37
- 42
29
votes
2 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,393
- 20
- 58
- 65