dcsimg

Eleven Quick Tips for SQL Server

October 23, 2001

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:\mssql\binn\sqlservr.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='SOFTWARE\Microsoft\Windows NT\CurrentVersion\', 
  @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.








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers