Internet solutions team

Following is very useful script which will give you occurrence of object or will tell you where you have used the object like field name is being used in stored procedures, functions and views.

SELECT
so.name,
so.TYPE,
sc.TEXT
FROM
sysobjects so
INNER JOIN syscomments sc
ON so.id = sc.id
WHERE  sc.TEXT LIKE ‘%[Field Name,Stored procedure name etc]%’

Following script will help to find column from all tables in database.

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE ‘%assetid%’
ORDER BY schema_name, table_name;

Following is code snipest which tells how to handle errors in SQL , T-SQL using Try Catch block. The code is self explanatory. This works excellent with Insert, Delete and Update SQL commands. Tested with Microsoft SQL server 2008.

USE [databasname]

BEGIN TRY

BEGIN TRANSACTION

Script code here;

COMMIT TRANSACTION

END TRY

BEGIN CATCH

IF @@TRANCOUNT > 0

ROLLBACK

DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int

SELECT @ErrMsg = ERROR_MESSAGE(),

@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)

END CATCH