Tag Info

Hot answers tagged

246

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.


61

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;


28

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


22

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

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

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

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

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

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

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

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

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

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


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

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

So the linefeed before peas and dhdhjsk should be skipped but the linefeed before save is part of the data. Which linefeeds should be skipped and which are part of the data? I think such a format is very error prone and should be avoided. Perhaps you can do it the following way but I did not verify this define "," as the physical record separator join ...


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?


2

go to ..\PostgreSQL\9.0\data and open the file postgresql.conf in text editor/notepad search for port parameter .eg: port = 5433 edit this to your port number. go to run type services.msc and restart postgresql service. you can check wether the parameter is set or not via query tool. just execute show port query. it will display your current port ...



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