Hot answers tagged

596

Please note the following commands: \list or \l: list all databases \dt: list all tables in the current database You will never see tables in other databases, these tables aren't visible. You have to connect to the correct database to see its tables (and other objects). To switch databases: \connect database_name See the manual about psql.


127

This lists databases: SELECT datname FROM pg_database WHERE datistemplate = false; This lists tables in the current database SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name;


46

\l is also shorthand for \list. There are quite a few slash commands, which you can list in psql by using \?.


45

In Postgresql these terminal commands list the databases available el@defiant$ /bin/psql -h localhost --username=pgadmin --list Or the command stated more simply: psql -U pgadmin -l Those commands print this on the terminal: List of databases Name | Owner | Encoding | Collate | Ctype | Access ...


18

From pg_Admin you can simply run the following on your current database and it will get all the tables for the specified schema: SELECT * FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema = 'public' ORDER BY table_type, table_name This will get you a list of all the permanent tables (generally the tables you're ...


12

To gain more info on database and table list, You can do : \l+ to list databases List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description ...


10

It is possible that you have inserted the tables into a schema that is not in your search path, or the default, ie, public and so the tables will not show up using \dt. If you use a schema called, say, data, you can fix this by running, alter database <databasename> set search_path=data, public; Exit and reenter psql and now \dt will show you the ...


9

Tom Kyte has a number of different solutions to generating flat files from Oracle on his site. There is a PL/SQL implementation using UTL_FILE as well as a Pro*C SQL unloader application.


8

You should look at the built in UTL_FILE package. There are several ways you could use it. You could write any number of procedures in packages that use the UTL_FILE package to write to any number of files. These procedures can then be called from almost any application including SQL*Plus. You could write a PL/SQL script to do the same work and call the ...


6

Creating a new instance The following command line call will create a new database + instance dbca -silent -createDatabase -templateName General_Purpose.dbc -gdbName sid -sysPassword pass -systemPassword pass because I have a dedicated oracle user for my install I also had to use -serviceUserPassword. With regards to the passwords, despite being in ...


5

By default MySQL server is using latin1 character set for each incoming connection. Latin1, as you might know, does not support cyrillic symbols. The simplest solution is to switch so called 'connection character set' by running SET NAMES 'utf8'; in the beginning of each connection. For example, this query should work: SET NAMES 'utf8'; CREATE TABLE ...


5

In a shell script: #!/bin/bash sqlplus user/pass@server/DATABASE<<THEEND -- Change "1" to the desired fatal return code whenever sqlerror exit 1; @yoursqlscript.sql quit; THEEND Or you can just run: sqlplus user/pass@server/DATABASE @yoursqlscript ... and put the whenever sqlerror exit 1; at the top of your .sql script(s).


5

Arun, now that sqlcl is available fromOracle SQL Developer 4.1 EA2 (4.1.0.18.37) you can use it a lot like the old and famous sqlplus. sqlcl has an output format setting for csv set sqlformat csv spool x.csv select * from yourtable[s]; spool off for more info about sqlcl checkout Kris' blog before sqlcl was around easiest for this was to use APEX and ...


5

You can use the following command (assuming that you have mysql set up in your PATH): mysql -h host -u user -p Just replace host and user with the correct values and then you should be prompted for your password. I hope this helps you.


5

There's nothing built-in from the command line. If you have Red Gate SQL Compare you can do it: sqlcompare /s1:MySQLInstance /db1:MyDB /mkscr:MyDB_Schema /q SSMS scripting functions are just wrappers for SMO. I know you mention it, but you could write a powershell script to use SMO. This is adapted from code found on this Simple Talk post. ...


5

Have a look at the Query SELECT md.host `Host`, md.user `User`, md.db `Database`, REPLACE(RTRIM(CONCAT( IF(md.Select_priv = 'Y', 'Select ', ''), IF(md.Insert_priv = 'Y', 'Insert ', ''), IF(md.Update_priv = 'Y', 'Update ', ''), IF(md.Delete_priv = 'Y', 'Delete ', ''), IF(md.Create_priv = 'Y', 'Create ', ''), IF(md.Drop_priv = 'Y', 'Drop ', ''), ...


4

If performance is a concern, you may want to consider tools from vendors. I have evaluated tools from BMC, Wisdomforce, CoSort, DBCrane. They are all significantly faster than spool, utl_file or external table. We are using DBCrane because my boss didn't want to spend too much on license.


4

To just feed off of squillman's answer this is to show a sample of what SQLPS can do for you...You can browse each "directory" under the database and just do a get-member -MemberType Method, looking for Script(). Most of the directories have it I believe. Add-PSSnapin *SQL* # Note my hostname of the server is "SQLSERVER" # To show object names to be ...


4

It appears that the root cause of this issue may have been that @Celeritas's computer had an incorrect value for the COMSPEC environment variable. It had a trailing semicolon, so instead of the normal: C:\Windows\system32\cmd.exe it was: C:\Windows\system32\cmd.exe; This one-character difference is enough. The above isn't a valid command prompt path, ...


4

The PostgreSQL command line client is called psql. You can do pretty much everything with it (apart from taking dumps, for example), you'd better reading its documentation. Try running locate psql in a terminal and you will find its binary. As far as I know, on OS X (and typically on other systems, too) pgAdmin is bundled with the server package, so you ...


4

@Phil's Answer and @Mr.Brownstone's Answer should suffice for your question, so +1 for both of them. For the following, let's assume you are logging in with username myuser Once you have connected to mysql, you should run the following query: SELECT USER(),CURRENT_USER(); USER() reports how you attempted to authenticate in MySQL CURRENT_USER() reports ...


4

Run with -vv, that will print the query being ran as well as the results you're looking for


4

Add -e option: · --execute=statement, -e statement Execute the statement and quit. The default output format is like that produced with --batch. See Section 4.2.4, “Using Options on the Command Line”, for some examples. With this option, mysql does not use the history file. mysql -uroot -pPassword1 -e "select column_name from ...


3

A conversation about this on GitHub led to an interim "quick fix" solution: open ~/.editrc and add: bind "^R" em-inc-search-prev Apparently the bigger issue is that PostgresApp uses libedit instead of libreadline. And supposedly libedit lacks some command-line features of libreadline, so until PostgresApp is compiled against libreadline, just use ...


3

I wrote an open source command line utility named SchemaZen that does this. It's much faster than scripting from management studio and it's output is more version control friendly. It supports scripting both schema and data. To generate scripts run: schemazen.exe script --server localhost --database db --scriptDir c:\somedir Then to recreate the database ...


3

PostgreSQL doesn't offer anything like that. You need to keep a second connection slot spare, or have a way to request termination via a side channel provided by the server host; say a web page that runs pg_terminate_backend as superuser to kill your connection. Alternately, you can find and kill the client side of the connection if you have local access ...


3

You are essentially attempting to dump the entire oplog and hence paging in all of it, not just the most recent data. That is going to cause a lot of IO, especially when your oplog is too large to fit into memory. On a smaller database it's not a problem because the oplog is relatively small. The reason why it's dumping the full table is because I believe ...


3

I suggest just writing a simple .NET console app to do just that. It can accept parameters for "ConnectionString" and "Query". This will be flexible enough to use in various situations. using System; using System.Data; using System.Data.SqlClient; namespace SimpleSqlCmd { class Program { static void Main(string[] args) { ...


3

PostgreSQL command-line utilities (and more generally all programs that rely on the libpq library) automatically use the environment variables PGPORT and PGHOST when they're defined. So if you do in the shell: $ PGPORT=5433; export PGPORT any subsequent call to psql will act as if it has been invoked with the -p 5433 command-line option. See Environment ...


3

You can use the SQL Server Database Publishing Wizard. If you don't have it installed then you can grab version 1.2 from here The installer doesn't give any indication that it has installed but if you open up a command window and navigate to: C:\Program Files (x86)\Microsoft SQL Server\90\Tools\Publishing\1.2 the SqlPubWiz.exe should be there. You can ...



Only top voted, non community-wiki answers of a minimum length are eligible