Using OSQL with Microsoft SQL databases

OSQL basic commands

Logging into a database with osql

c:\> osql -S [servername\instance] -U username [-P password]

Example1: Specifying the user and letting the command line prompt for a password.

c:\> osql -S Core\ldmsdata -U sa
password:

Example2: Specifying the user and the password.

c:\> osql -S ld87\ldmsdata -U sa -P pw

Example3: Using a trusted connection

c:\> osql -E

Show databases

1>
2>
select * from sysdatabases
go

Note: Or to see only the database Name row do the following:

1>
2>
select Name from sysdatabases
go

Creating a database

1>
2>
create database DatabaseName
go

Selecting a database

1>
2>
USE master
go

Drop a database

1>
2>
drop database DatabaseName
go

Show tables

1>
2>
1>
2>
USE DatabaseName
go
select * from INFORMATION_SCHEMA.TABLES
go

Note: Or to see only the Table_Name row do the following:

1>
2>
1>
2>
USE DatabaseName
go
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
go

Drop a table

1>
2>
1>
2>
USE DatabaseName
go
drop table TableName
go

Insert a row into a table

1>
2>
1>
2>
USE DatabaseName
go
INSERT INTO TableName Values(“Column1value”,”Column2value”,”Column3value”)
go

Note: Or to insert by only providing values for a few columns and letting the other columns take the default values. This is useful when the first column is set to AUTO_INCREMENT.

1>
2>
1>
2>
USE DatabaseName
go
INSERT INTO TableName (Col2, Col3) Values(“Column2value”,”Column3value”)
go

Update a value in row of a table

1>
2>
1>
2>
USE DatabaseName
go
UPDATE TableName set ColumnName=’NewValue’ where SomeColumn=’whereValue’
go

Drop a view

1>
2>
1>
2>
USE DatabaseName
go
drop view ViewName
go

Backup a database

1>
2>
BACKUP DATABASE ulddb TO DISK=’c:\path\to\dbbackup.bak’ WITH FORMAT
go

You can do this at the command prompt with one single command:

c:\> osql -S ld87\ldmsdata -U sa -P pw -Q “BACKUP DATABASE ulddb TO DISK=’c:\path\to\dbbackup.bak’ WITH FORMAT”

Change the SA password with one line in a command prompt

c:> osql -E -S localhost\ldmsdata -d ULDDB -Q “sp_password NULL,’P@ssword’,’sa’”

I hope this helps you.

More Information

For more information see the following website:

  1. Administering SQL Server Using osql
  2. osql Utility

Leave a Reply

Powered by sweetCaptcha