Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 23, 2001

Eleven Quick Tips for SQL Server

By Neil Boyle

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM