SQL error handling

Use try/catch to handle errors

BEGIN TRY
   . . .
END TRY
BEGIN CATCH
  ... (here you handle error and re-throw exception if you want. To throw exception you can do
       something like this RAISERROR('your error msg', 16, 1). Severity must be 16
END CATCH

The following system function provide error info. It can be used to format error message for logging or re-throwing within from CATCH block.

• 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.

How to query database schema and objects

Run select statement against system object information_schema.tables for tables list and information_schema.columns for columns list

Example

Select table_name from information_schema.tables where table_name like '%cust%'

Select column_name from information_schema.columns where table_name = 'customers'

Sp sp_help [object] can be also helpful

How to find stored procedure that contains the given text

SELECT DISTINCT so.name FROM sys.sysobjects so 

INNER JOIN sys.syscomments sc ON sc.id=so.id 

WHERE sc.TEXT LIKE '%your tesxt%'