Here are a few useful code fragments that I use all the time.

You see too many Rows Affected Messages.

First, NOCOUNT. If you don’t want SQL Server to return the Row Count

(20 row(s) affected)

after you execute a query, here is the code:

— Turn off the rows affected message

SET NOCOUNT ON

Once you have executed your queries, you can enable the row count again

SET NOCOUNT OFF

ANSI Warnings interrupt your inserts

Next, do you get annoying ANSI errors when inserting data? You can remove those warnings using one simple command.

SET ANSI_WARNINGS OFF

If there is a failure, the inserts will fail. However, now you can skip those warnings and proceed with the inserts.

Finding and Dropping Temp Tables

Lastly, when working with true temp tables, the ones starting with ## or #, the old standard will not work. This is because Temp tables live in the database TempDB, not your current DB. SO, sysobjects won’t have the table info you’re looking for. The firs t snippet is what you would use for a standard table in your CURRENT database.

-- Won't Work for #temp tables!
-- Will only work for regular tables that exist in your current database.
IF EXISTS (SELECT * FROM sysobjects WHERE NAME='##tmpTable')
BEGIN
     Truncate table ##tmpTable
     DROP TABLE ##tmpTable
END

The strategy changes just a bit to find and drop a temp table. Instead of searching in your current database, you’re going to have to look and see if an object exists in TempDB. To do this, we will use the OBJECT_ID command. The OBJECT_ID command will return the ID for the requested object, if it exists. If OBJECT_ID returns a number, we know the temp table exists. Here is the script:

-- This script will delete temp tables
IF OBJECT_ID('tempdb..##tmpTable') IS NOT NULL 
BEGIN
     Truncate table ##tmpTable
     DROP TABLE ##tmpTable
END

The temp table is now gone and ready to be recreated, as needed.