Eleven Quick Tips for SQL Server

Quickly count all the rows in all the tables in a database

The ‘rows’ column in the system table ‘sysindexes’, which exists in any SQL
Server database, will hold the number of committed rows in the
table. The “indid < 2" clause restricts the query to looking at the clustered index or heap info, and ignores subsequent indices. Note that only the results of committed transactions are held in the rows column--uncommitted transactions are not.

Click here for code example 1.

Use a table name as a parameter to a stored
procedure

You need to use dynamic SQL for this, as the SQL Interpreter
is not smart enough to deal with a straight parameter substitution
in these cases.

Click here for code example 2.

See this article for more information on dynamic SQL

Create a script to BCP out all your tables

Click here for code example 3.

The above example assumes that all the database objects are
owned by the database owner or DBO.

This will create a script that you can save and run as a Windows
Command file or as a “batch” file. The output will be tab-delimited
text.

Run BCP /? in a command window, or look up BCP in Books Online
if you need more information about the command parameters.

You can wrap each line up in a xp_cmdshell command and run
it from ISQL if you wish. Remember, though, that drive letters in
your file path with them correspond to those on the server, not
the ones on your client PC.

You should do this if you need the
target files to reside on the server or any machine other than
the client you are working from–it cuts down the network traffic
involved in sending the data via your PC.

It is worth mentioning the /b option–this splits the bcp operation up into
(in this example) 1000-line transactions. This is more important when BCPing
large files into a database, because using bcp without the /b option will cause
SQL Server to treat the entire operation as one single operation.

Checking if a file exists on disk

Use the extended stored procedure
xp_getfiledetails to access file information on the server.

Master..xp_getfiledetails "c:mssqlbinnsqlservr.exe"

Checking for free disk space

Master..xp_fixeddrives

The stored procedure master..xp_fixeddrives reports all the
free space available on your servers fixed disks. Be warned, though, if running on SQL
Server 6.5–the “Bytes free” column is not actually Bytes free,
but Megabytes free.

Shut down a remote machine without leaving
your chair

The Windows NT Resource Kit comes with two utilities that can
do this. They are called “shutdown” and “shutgui”.
Be aware that rebooting the machine afterwards is not the default option,
so if you get it wrong you will have to get out of your chair
after all.

View extended Version Information

Master..xp_msver

Cycle the SQL Server event log

Especially in production environments, you can find that the SQL Server event
log gets unmanageably large after a while. Many people think that the only way
to clear down the log is to stop and restart SQL Server, but you can also
achieve this using the SQL Server command dbcc errorlog.

Note that this is an undocumented command–these are usually undocumented
for good reason, so use it at your own risk.

Reading the server Registry

You can read the contents of the server registry using the extended stored
procedure XP_REGREAD–this example displays NT 4 Service Pack versioning
information from the servers registry:

master..xp_regread 
  @rootkey='HKEY_LOCAL_MACHINE', 
  @key='SOFTWAREMicrosoftWindows NTCurrentVersion', 
  @value_name='CSDVersion'

“Autoexec” stored procedures

You can make one or more stored procedures invoke automatically every time SQL Server starts.
You can use this feature to raise an alert in case of unscheduled reboots, log SQL Server uptimes, or start
processes that depend on SQL server being activated.

SQL 6.5: To do this, create your stored procedure in the master database and execute the command: sp_makestartup "procedure_name"

SQL 7 / 2000: Use the sp_procoption
stored procedure instead.

Recover databases without backups

Of course, you can ignore this tip, because you check and test
your backups regularly, don’t you?

However, if somebody you know does get into this sticky situation, tell them
you can often recover data by recovering all the .LDF (log files) and .MDF files
for your database and re-attaching them to SQL Server using the stored procedure
sp_attach_db.