SQL Server 2005 Tips and Tricks

Exception Handling in SQL Server 2005
Structured exception handing provides a powerful mechanism for controlling complex programs that have many dynamic runtime characteristics
The TRY block contains transactional code that could potentially fail, while the CATCH block contains code that executes if an error occurs in the TRY block. If any errors occur in the TRY block, execution is diverted to the CATCH block and the error can be handled while error functions can be used to provide the detailed error information. TRY…CATCH has the following abbreviated syntax:

BEGIN TRY
RAISERROR ('Houston, we have a problem', 16,1)
END TRY

BEGIN CATCH
SELECT ERROR_NUMBER() as ERROR_NUMBER,
ERROR_SEVERITY() as ERROR_SEVERITY,
ERROR_STATE() as ERROR_STATE,
ERROR_MESSAGE() as ERROR_MESSAGE
END CATCH

Notice the use of functions in the script above that we are able to use in place of local and/or global variables. These functions should only be used in a CATCH BLOCK and are explained below:

ERROR_NUMBER() returns the number of the error.
ERROR_SEVERITY() returns the severity.
ERROR_STATE() returns the error state number.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names or times.




Check if a database or table exists using Sql Server 2005
The Object_ID() function in SQL Server can be utilised in a number of ways. One such utility is to verify if an object exists.

The Object_ID() takes in the object name and object type as parameters. The object name is the object used and the object type is the type of object used in a schema.

For example to check if a table exists in a database, use this query :

IF OBJECT_ID ('AdventureWorks.dbo.AWBuildVersion','U') IS NOT NULL
Print 'Table Exists'
ELSE
Print 'Table Does Not Exists'

where 'AdventureWorks.dbo.AWBuildVersion' is the object name and 'U' is the object type which represents a table

Similarly you can check for a stored procedure or a view by specifying the correct object type. You can get an entire list of object types over here.

To check if a database exists, you can use the DB_ID() function as shown below :

IF db_id('AdventureWorks') IS NOT NULL
Print 'Database Exists'
ELSE
Print 'Database Does Not Exists'
Display the size of all tables in Sql Server 2005
sp_spaceused returns number of rows, disk space reserved, and disk space used by a table. However when you have to return the space used by all the tables in a database, you have two options: One is to loop through all the tables and then pass the table name to the sp_spaceused procedure. The second is to use the undocumented sp_MSforeachtable procedure. We will explore both of these over here:

The Lengthy Way


USE yourdbname
DECLARE @TblNames Table
(
COUNTER INT IDENTITY(1,1),
tbl_name nvarchar(100) NULL
)
DECLARE @ROWCOUNT INT
DECLARE @I INT
DECLARE @str nvarchar(100)
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT name FROM sys.Tables
SET @ROWCOUNT = @@ROWCOUNT
WHILE @I <= @ROWCOUNT
BEGIN
SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I
EXEC sp_spaceused @str
SET @I = @I +1
END

Note: The advantage in taking the lengthy approach is that you can create another temporary table and sort the tables based on the space used.

The Short Way

USE yourdbname
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

Note: sp_MSforeachtable is an undocumented stored procedure

References :
http://msdn2.microsoft.com/en-us/library/ms188776.aspx
http://www.msnewsgroups.net/group/microsoft.public.dotnet.languages.csharp/topic37975.aspx
http://www.mssqlcity.com/Articles/Undoc/SQL2000UndocSP.htm

Execute a T-SQL statement at a given time
The 'WAITFOR' command is used to delay the execution of a batch, stored procedure or transaction till a specified time duration or till an actual time. Let me demonstrate this:

Create a DELAY for a specific amount of time

USE NORTHWIND
WAITFOR DELAY '00:01:00'
BEGIN
SELECT CustomerID, CompanyName, ContactName FROM CUSTOMERS
END

Delays the execution of the T-Sql statement by 1 minute. To delay by an hour, you would use '01:00:00'. You can specify a maximum of 24 hours.

Execute at the given time (actual time)

USE NORTHWIND
WAITFOR TIME '11:23:00'
BEGIN
SELECT CustomerID, CompanyName, ContactName FROM CUSTOMERS
END

Delays the execution of the T-Sql statement till the time '11:23 A.M'. You cannot specify a date, only time is allowed.