Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
243
votes
18 answers

How do you document your databases?

I find that most of my clients are not documenting their databases at all and I find that pretty scary. To introduce some better practice, I would like to know what tools/process people are using. How do you document your database? (SQL-Server)…
user316
  • 961
  • 3
  • 7
  • 4
218
votes
6 answers

Force drop db while others may be connected

I need to remove a database from a PostgreSQL DB cluster. How can I do it even if there are active connections? I need sort of a -force flag, that will drop all connections and then the DB. How can I implement it? I'm using dropdb currently, but…
Alex
  • 2,375
  • 2
  • 16
  • 7
212
votes
3 answers

Granting a user account permission to create databases in PostgreSQL

How do I give a user account in PostgreSQL the ability to create and drop databases? Is there a way to do this with GRANT?
GSto
  • 2,223
  • 2
  • 13
  • 6
211
votes
2 answers

Create a MySQL database with charset UTF-8

I'm new to MySQL and I would like to know: How can I create a database with charset utf-8 like I did in navicat? create mydatabase; ...seems to be using some kind of default charset.
user3397998
  • 2,221
  • 2
  • 11
  • 4
203
votes
7 answers

How can I optimize a mysqldump of a large database?

I have a symfony application with an InnoDB database that is ~2GB with 57 tables. The majority of the size of the database resides in a single table (~1.2GB). I am currently using mysqldump to backup the database nightly. Due to my comcast…
Patrick
  • 4,179
  • 7
  • 27
  • 27
202
votes
5 answers

When should a primary key be declared non-clustered?

While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED When would you use a NONCLUSTERED primary key as opposed to a CLUSTERED primary key? Thanks in advance
Stuart Blackler
  • 4,462
  • 7
  • 27
  • 42
201
votes
5 answers

Granting access to all tables for a user

I'm new to Postgres and trying to migrate our MySQL databases over. In MySQL I can grant SELECT, UPDATE, INSERT, and DELETE privileges on a low privileged user and enable those grants to apply to all tables in a specified database. I must be…
PlaidFan
  • 2,115
  • 2
  • 13
  • 7
193
votes
17 answers

How do I find PostgreSQL's data directory?

I forgot how I started PostgreSQL the last time (it was months ago) and I don't remember where the data directory is located. The postgres command seems to require the location of the data directory. I'm on MacOsX if that helps. /usr/local/postgres…
randomguy
  • 2,081
  • 2
  • 13
  • 6
189
votes
4 answers

'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

I am trying to backup mysql using the command mysqldump -u root -p database_name > backup.sql , but it is throwing an error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump…
subhanshu kumar
186
votes
7 answers

What's the difference between a CTE and a Temp Table?

What is the difference between a Common Table Expression (CTE) and a temp table? And when should I use one over the other? CTE WITH cte (Column1, Column2, Column3) AS ( SELECT Column1, Column2, Column3 FROM SomeTable ) SELECT * FROM…
Rachel
  • 8,367
  • 20
  • 48
  • 74
184
votes
5 answers

How to get the name of the current database from within PostgreSQL?

Using \c in PostgreSQL will connect to the named database. How can the name of the current database be determined? Entering: my_db> current_database(); produces: ERROR: syntax error at or near "current_database" LINE 1:…
Amelio Vazquez-Reina
  • 1,967
  • 2
  • 13
  • 8
177
votes
12 answers

SQL: SELECT All columns except some

Is there a way to SELECT all columns in a table, except specific ones? IT would be very convenient for selecting all the non-blob or non-geometric columns from a table. Something like: SELECT * -the_geom FROM segments; I once heard that this…
Adam Matan
  • 11,019
  • 29
  • 79
  • 94
174
votes
19 answers

Should developers be able to query production databases?

Should developers be given permission to query (SELECT / read only) production databases? The previous place I worked, the development team had the db_datareader role; where I work now the development team can't even connect to the production…
Tom Hunter
  • 2,139
  • 3
  • 16
  • 11
172
votes
4 answers

Help installing SQL Server 2017 - VS Shell installation has failed with exit code 1638

Any suggestions on how to deal with this error: TITLE: Microsoft SQL Server 2017 Setup ------------------------------ The following error has occurred: VS Shell installation has failed with exit code 1638. For help, click:…
Jonathan Allen
  • 3,526
  • 6
  • 23
  • 24
163
votes
6 answers

Guid vs INT - Which is better as a primary key?

I've being reading around reasons to use or not Guid and int. int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is unique. In which case a Guid would be better than and…
BrunoLM
  • 3,313
  • 7
  • 26
  • 22
1
2
3
…
99 100