sqlcmd
C:\> sqlcmd -?
C:\> sqlcmd -S (local) -E -d
master -Q "SELECT name FROM
sys.databases"
You can run a script without having to use interactive mode, too. All you have to do is add a different switch instead of the -Q parameter I demonstrated earlier:
C:\> sqlcmd -S (local) -E -d master -i C:\test.sql
The -o switch outputs the results of the query to a file rather than the screen. To send the results of the preceding query to a file called results.txt, use this command:
C:\> sqlcmd -S (local) -E -d master -i C:\test.sql -o C:\results.txt
bcp
The format of the command to copy data from the sys.databases table to a text file looks like this:
C:\>bcp -?
C:\>bcp master.sys.databases out "c:\test.txt" -T -c
In this example, I have specified the bcp command and then the database, owner, and table that I am after. Next I told the utility which direction the data should goin this case, out. I also specified the name of the file that will hold the data. The -T (notice the uppercase) sets a trusted connection, and the -c sets the column types to the character, or text format. If I you use -n, the file will be stored in native mode, which is a binary format only SQL Server can read.
To export data using a view, you just specify it as if it were a table. To export data using a query, use the queryout switch, which looks like this:
C:\>bcp "SELECT name FROM master.sys.databases" queryout "c:\test.txt" -T -c
The -f switch tells the bcp utility to use a format file, which is just a text file that allows you to use other column names and do field mapping. Here are the first few lines from a sample format file I created:
9.0 4 1 SQLCHAR 0 15 "\t" 1 Name ""
Along with sqlcmd and bcp, other useful command-line utilities include those listed in the following table.
|
Command |
Used For |
|---|---|
|
osql |
This is an older command similar to sqlcmd. It uses an older library to access the server and does not have as many options as sqlcmd. |
|
cidump |
This command lists the contents of an indexing service catalog. |
|
DTexec |
This command configures and executes a Data Transformation Services (DTS) package. |
|
dtutil |
This is used to manage DTS packages. |
|
dtswizard |
This command starts the DTS Import/Export Wizard. |
|
dta |
This command is used to analyze a workload. It can also recommend another physical design. |
|
lrtest |
This command invokes the Full Text Search. |
|
nscontrol |
This command creates and controls Notification Services. |
|
profiler90 |
This command starts the SQL Profiler from a command prompt. |
|
rs |
This command runs scripts for Reporting Services. |
|
rsactivate |
This is another command for Reporting Services and initializes a report server. |
|
rsconfig |
This command configures a report server connection. |
|
rskeymgmt |
Another Reporting Services command tool, this one manages encryption keys on a report server. |
|
This command starts SQL Server Agent. |
|
|
sqlservr |
If you need to start or stop the SQL Server database engine from the command prompt, use this command with options to specify how it will start. I explain this command further in Chapter 3. |
|
sqlmaint |
Maintenance plans are groups of jobs used to optimize and back up a database. I explain these further in Chapter 3. This command is used to run those maintenance plans created in previous versions of SQL Server. |
|
tablediff |
This is a welcome new command that can compare the data in two tables. It is used a great deal in troubleshooting replication. |
插入表情