Friday, October 15, 2004

Cool MS SQL Server Tools

Sqldiag - Sqldiag is a utility used for report generation and collection of diagnostic information on database server / operating system configuration parameters. Sqldiag gathers the information, even if Microsoft SQL Server 2000 services are stopped. The report generated by Sqldiag contains the following information:
Complete dump of all SQL Server error logs; Registry information, related to SQL Server; SQL Server system dll versions; Output, generated by: sp_configure, sp_who, sp_lock, sp_helpdb, xp_msver and sp_helpextendedproc; Information about all system processes (master..sysprocesses); Information about all user processes and connections (including Input buffer SPIDs and Dead locks); Information about operating system parameters (including reports about: OS version, Video display, Drivers, DMA, Memory, Services, IRQ and ports, Devices, Environment and Network); Info about the last 100 user's queries. The Sqldiag utility is installed to the \Program Files\Microsoft SQL Server\MSSQL\Binn directory by default.

Profiler - Profiler is the executable for SQL Server Profiler. SQL Server Profiler is typically used for monitoring SQL Server events, such as debugging T-SQL statements and stored procedures and troubleshooting problems (by capturing them in a real-time and replaying later).

Sqlmaint - Sqlmaint is a maintenance utility. Sqlmaint performs a set of tasks, specified by the DBA, on one or more databases (for example backup databases, update statistics, rebuild indexes, DBCC check).
The Sqlmaint utility is installed to the \Program Files\Microsoft SQL Server\MSSQL\Binn directory by default

bcp - A utility used for the interactive process of bulk coping of data between the SQL Server 2000 instance and data file (format information file should be specified or a default bcp.fmt should be used instead). The bcp utility is the typical example of a "two-way" tool, i.e. copying data "into SQL Server instance" or "out of SQL Server instance" is allowed. Alternatively, bcp can be used for copying data:
Between SQL Server instances with different language collations; To or from a view; Returned from a T-SQL query (to data file); Between the Microsoft SQL Server and database servers of other vendors; Between the SQL Servers working on different processor architectures; To or from a database table (including temporary tables); Between databases within one SQL Server instance. The bcp utility is installed by default to the \Program Files\Microsoft SQL Server\80\Tools\Binn directory

itwiz - itwiz allows the Index Tuning Wizard to be executed from a command prompt. Index tuning using an itwiz is similar to tuning via Index Tuning Wizard with a user interface. The itwiz utility is installed to the \Program Files\Microsoft SQLServer\80\Tools\Binn directory by default.

osql - A utility for interactive Transact-SQL scripts and stored procedures execution. It uses ODBC libraries for communicating with the database server. Osql can be started directly from the operating system command prompt and uses a standard output device (monitor, by default) for displaying results. The osql utility is installed to the \Program Files\Microsoft SQLServer\80\Tools\Binn directory by default.

Simple Enabling/Disabling Constraints/Triggers on the entire SQL 2000 Database
sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? CHECK CONSTRAINT all"sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER all"

1 comment:

Anonymous said...

i never knew of index tuning wizard!
also the command to disable and enable constraints was of great help!