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.
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.
See this article for more information on dynamic SQL
Create a script to BCP out all your tables
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.
Checking for free disk space
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
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:
"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:
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.