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"

Simple SQL Server/MSDE Database Installation through osql

Here, a MSDE database is dropped, attached, and a user is given rights on the db
With minor changes to the osql parameters a Server name can be provided and this script will work for an SQL 2000 database.

--drop old db
osql -E -S -Q "DROP DATABASE [dbname]"

--copy the mdf to the target loc
copy "c:\installtemp\dbname*.?df" "C:\program Files\Microsoft SQL Server\MSSQL\Data"

--make sure the db file is not read only
attrib -r "C:\program Files\Microsoft SQL Server\MSSQL\Data\dbname*.?df"

--attach the db to the target instance/server
osql -E -S -Q "EXEC sp_attach_db @dbname = 'dbname', @filename1 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\dbname_Log.LDF', @filename2 = N'C:\Program Files\Microsoft SQL Server\MSSQL\Data\dbname_Data.MDF'"

use dbname
--add a user to access this db apart from default db admin user
EXEC sp_grantdbaccess "domain\user", "domain\user"
GO

--grant read access to this user
exec sp_addrolemember N'db_datareader', "domain\user"
GO

--grant write access to this user
exec sp_addrolemember N'db_datawriter', "domain\user"
GO

--security script to make sure sps and fns have exec priv.
osql -E -S -Q -i "c:\installtemp\dbnameSecuritySetup.sql"

--Security script is below -- dbnameSecuritySetup.sql
--recurively grant exec priv to all sps and fns in db.
USE dbname
DECLARE @sExecQry sysname
DECLARE EXEC_SPS CURSOR LOCAL FOR
select 'grant exec on ' + QUOTENAME(name) + ' to "domain\user" ' from sysobjects where (type = 'P' or type='FN') and objectproperty(id,'IsMSShipped')=0
OPEN EXEC_SPS
FETCH NEXT FROM EXEC_SPS INTO @sExecQry
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(@sExecQry)
--PRINT @sExecQry -- debug only
FETCH NEXT FROM EXEC_SPS INTO @sExecQry
END
CLOSE EXEC_SPS
GO

--Perform checks on the database
DBCC CHECKCONSTRAINTS WITH ALL_CONSTRAINTS
GO

DBCC CHECKDB
GO

DBCC CHECKALLOC
GO

DBCC CONCURRENCYVIOLATION
GO

DBCC DROPCLEANBUFFERS
GO

DBCC FREEPROCCACHE
GO

DBCC UPDATEUSAGE(0)
GO