Tag Info

Hot answers tagged

277

Please note the following commands: \list lists all databases \dt lists 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). See the manual about psql.


66

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;


33

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


24

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 ...


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 ...


7

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 ...


7

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 ...


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

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 ', ''), ...


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).


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

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


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

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 ...


3

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.


3

This is happening because the auto-complete isn't context aware & it's offering you a column name of widget_date as well as the table name you're expecting.


3

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, ...


3

I wrote a command line utility named schemazen that does this. It's much faster than scripting from management studio and it's free and open source. To generate scripts run: schemazen.exe script --server localhost --database db --scriptDir c:\somedir Then to recreate the database from scripts run: schemazen.exe create --server localhost --database db ...


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 ...


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

Nicholas Cain (@SirSQL on Twitter) published three blogs recently regarding the installation of SQL on a WIndows Failover Cluster using PowerShell to script the activity. The first of the three blog entries may be found at http://sirsql.net/blog/2011/7/11/creating-a-clustered-sql-install-ini-file-using-powershellpa.html The second of the three blog entries ...


3

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 ...


3

Since you're on Windows and are probably starting PostgreSQL as a Windows service, you'll need to edit postgresql.conf (inside your data directory) to set the new port there, then restart the postgresql service using the Services control panel or (as an Administrator) the net service command. You can use a programmatic text editing tool to change ...


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 ...


2

Arun, easiest for this is to use APEX and export the report to csv. In plain old sqlplus you can do this by using set lines 9999 -- the appropriate size set head off -- no header lines set colsep ';' --column separator to ; set pages 0 -- no pages set feed off select your data; spool to_tofile / spool off This works best if the results are to be written ...


2

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 ...


2

@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 ...


2

mysql --user=user_name --password=your_password dbname Is your question that simple, or is there a specific case you need to test?



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