Find text in Stored Procedures

The following query returns all delete stored procedures with a delete clause;

SELECT

    *

FROM

    INFORMATION_SCHEMA.ROUTINES

WHERE

    ROUTINE_TYPE = 'PROCEDURE'

    AND ROUTINE_DEFINITION LIKE '%DELETE%'

Query Speed Testing

I noticed that when optimising a query it can be quite difficult. SQL server caches table information so it can be difficult to test how fast a query is running.

To clearr the cache, run the following query;

--Remove execution plan cache

DBCC FREEPROCCACHE

GO

--Remove data cache

DBCC DROPCLEANBUFFERS

GO

Please note, you should never do this on a live server!!

SQL Server 2008 R2 File Extensions & file groups

SQL Server uses the following three files types to store information;

.mdf - main database file
.ndf - (n) database file
.ldf - Log data file


When you create a database via the gui, by default you are given an .mdf file and a .ldf file. You are also given a PRIMARY file group, which contains the .mdf file.

A filegroup can contain one or more datafiles.

Sometimes, it can be beneficial to have more than one filegoup, for example for best practice you can separate the default system objects, which are created by the gui when you first create the database. You can create a secondary filegroup, with an .ndf file and then make this your primary file group once you have created the datanbase. This would provide you with greater isolation from I/O write activity.

Find All Tables with Column Name

When working with large databases, it's often difficult to track down related tables. The following SQL works for SQL Server 2008;

SELECT 
   OBJECT_NAME(object_id), * from sys.columns 
where 
   name like '%col_name%';

Simple replace col_name with the name of the column you are searfching for.

SQL Server @@ERROR Example

I've been informed that using @@ERROR isn't the most robust way of error checking. An old example would be the following;

BEGIN TRANSACTION
 EXEC sp_deleteuser 1
 IF @@ERROR != 0 --RollBack Transaction if Error..
  BEGIN
  ROLLBACK TRANSACTION
  END
 ELSE
  BEGIN
  COMMIT TRANSACTION --finally, Commit the transaction if Success..
  END

Instead of using the above example, you should be using the following;

BEGIN TRY
 BEGIN TRANSACTION
 EXEC sp_deleteuser 1
 IF @@TRANCOUNT > 0
  COMMIT TRANSACTION
END TRY
BEGIN CATCH
 --Return error if any
 SELECT
  @errNo = ERROR_NUMBER()
  , @errMsg = ERROR_MESSAGE()
  , @errSeverity = ERROR_SEVERITY()
  , @errState = ERROR_STATE()
   IF @@TRANCOUNT > 0
       ROLLBACK TRANSACTION
 --Raise the error
   RAISERROR (@errMsg, @errSeverity, @errState)
END CATCH