Common OSQL commands for use with MSDE

One of the primary differences between the full SQL Server package and MSDE is the lack of a GUI utility to query the database. MSDE offers a command-line tool called OSQL which provides database querying ability.

Below are some common OSQL commands which may be helpful for system administrators:

To connect to MSDE from the command-line using built-in authentication:

osql -S <server>\<instance> -E

To list all databases on the server:

use master;
go
select name from sysdatabases;
go

To list all tables in a database:

use <database>;
go
select name from sysobjects where type = 'U';

To list all users and roles in a database:

use <database>;
go
select name from sysusers;
go

To add a Windows group:

exec sp_grantlogin '<domain>\<group>';
use <database>;
exec sp_grantdbaccess '<domain>\<group>','<newusername>';
go

To add a role to a user:

use <database>;
exec sp_addrolemember '<rolename>', '<username>';
go

Show the version and service pack level of the MSDE install:

select @@VERSION;
go

Backup a database:

BACKUP DATABASE <DBname> TO DISK = '<fullpath>.dat'

Author: ASAK
Created: Mar 8 2006
Categories: MSDE/SQL Server Express
TechByte #128

Warning: By visiting this site and/or by using any information contained herein, you agree to the Techbytes.ca terms of use.