declare @search varchar(50)
SET @search = 'searchterm'

SELECT    
     ROUTINE_NAME,
     ROUTINE_DEFINITION
FROM    
    INFORMATION_SCHEMA.ROUTINES
WHERE    
    ROUTINE_DEFINITION LIKE @search
ORDER BY
    ROUTINE_NAME

CREATE PROCEDURE [dbo].[genericCrosstab]

 -- @tableSpec
 --   A comma delimited list of columns with datatypes (and null constraints, if required)
 --   e.g. 'col1 INT, col2 VARCHAR(255) NULL'
 --   N.B. You only need provide this list if the source of the data is a stored procedure
 @tableSpec    NVARCHAR(4000) = '',

 -- @sqlSelect
 --   The SELECT clause (or 'EXEC <stored_procedure>' statement) for the input data
 --   e.g. 'SELECT a.au_id, a.au_fname, a.au_lname, a.phone, LEFT(t.title, 10) AS [title], LEFT(s.stor_name, 10) AS [store], sales.qty'
 --   e.g. 'EXEC dbo.myStoredProcedure <param1>, <param2>'
 @sqlSelect    NVARCHAR(512),

 -- @sqlFromWhere
 --   The FROM and WHERE clauses for the input data (if a SELECT statement)
 --   e.g. 'FROM authors a, titleauthor ta, titles t, stores s, sales WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id AND t.title_id = sales.title_id AND s.stor_id = sales.stor_id'
 --   N.B. Leave this blank if the source of the data is a stored procedure
 @sqlFromWhere NVARCHAR(512)  = '',

 -- @pivotExpr
 --   The column name or expression to use as the pivot (specifies which values are the column headings for the crosstabbed data)
 --   e.g. 'store' or '''Store: '' + [store]'
 @pivotExpr    NVARCHAR(255),

 -- @valueExpr
 --   A column name or expression to use as the values in the crosstabbed data
 --   e.g. 'qty'
 @valueExpr    NVARCHAR(512),

 -- @function
 --   The aggregation function to use to combine values in the crosstabbed data
 --   e.g. 'MIN'
 --   N.B. If this parameter is left blank, the procedure will count up occurences and create additional column headings as necessary, e.g. '[X], [Y], [Y (2)], [Z]' if there are two occurences for label 'Y'
 @function     NVARCHAR(20)   = '',

 -- @groupBy
 --   The column list to group by (these columns will appear to the left of the crosstabbed data)
 --   e.g. 'au_id, au_fname, au_lname, phone'
 @groupBy      NVARCHAR(512),

 -- @sortLookup
 --   A table or view which can be queried to provide a sort order for the columns in the crosstabbed data
 --   e.g. 'days_of_the_week'
 --   N.B. The specified table or view must have 'label' and 'sort_order' columns
 --   N.B. If column order is irrelevant, leave this parameter blank
 --   N.B. Feature: if @sortLookup is given as '*', the columns are placed in ascending alphabetical order
 @sortLookup   NVARCHAR(40)   = '',

 -- @collation
 --   The collation sequence
 --   e.g. 'Latin1_general_CI_AS'
 @collation   NVARCHAR(40)   = 'Latin1_general_CI_AS',

 -- @verbose
 --   Set to 1 if you want debug messages
 @verbose     BIT            = 0

AS
BEGIN

 -- Check that the mandatory parameters are not empty strings
 IF LEN(RTRIM(ISNULL(@sqlSelect, ''))) = 0
 BEGIN
   RAISERROR ('The @sqlSelect parameter cannot be an empty string.', 16, 1)
   ROLLBACK TRANSACTION
 END

 IF LEN(RTRIM(ISNULL(@pivotExpr, ''))) = 0
 BEGIN
   RAISERROR ('The @pivotExpr parameter cannot be an empty string.', 16, 1)
   ROLLBACK TRANSACTION
 END

 IF LEN(RTRIM(ISNULL(@valueExpr, ''))) = 0
 BEGIN
   RAISERROR ('The @valueExpr parameter cannot be an empty string.', 16, 1)
   ROLLBACK TRANSACTION
 END

 IF LEN(RTRIM(ISNULL(@groupBy, ''))) = 0
 BEGIN
   RAISERROR ('The @groupBy parameter cannot be an empty string.', 16, 1)
   ROLLBACK TRANSACTION
 END

 -- Local variables
 DECLARE @i      INTEGER
 DECLARE @sql    NVARCHAR(4000)
 DECLARE @sqlX   NVARCHAR(4000)
 DECLARE @col    NVARCHAR(4000)
 DECLARE @pivot  NVARCHAR(4000)
 DECLARE @indx   NVARCHAR(10)
 DECLARE @cols   NVARCHAR(4000)
 DECLARE @where  NVARCHAR(4000)
 DECLARE @update NVARCHAR(4000)
 DECLARE @value  NVARCHAR(532)
 DECLARE @select NVARCHAR(4000)

 -- Drop the global temporary tables we will use (if they already exist)
 -- N.B. we have to use global temp tables, as local temp tables are dropped at the end of a batch (so are not accessible outside of the sp_executesql context)
 -- This syntax is a bit long winded because I couldn't make the substitution syntax for sp_executesql work (for this example only! - a bug in T-SQL?)
 SELECT @sql = N'IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name = ''@table'' AND xtype = ''U'') DROP TABLE @table'
 SELECT @sqlX = REPLACE(@sql, N'@table', N'##input')
 EXEC sp_executesql @sqlX
 SELECT @sqlX = REPLACE(@sql, N'@table', N'##grpCount')
 EXEC sp_executesql @sqlX
 SELECT @sqlX = REPLACE(@sql, N'@table', N'##colList')
 EXEC sp_executesql @sqlX
 SELECT @sqlX = REPLACE(@sql, N'@table', N'##lookup')
 EXEC sp_executesql @sqlX
 SELECT @sqlX = REPLACE(@sql, N'@table', N'##results')
 EXEC sp_executesql @sqlX
 SELECT @sqlX = REPLACE(@sql, N'@table', N'##temp')
 EXEC sp_executesql @sqlX

 IF @verbose = 1 SELECT 'Global temporary tables dropped (if present).' AS [Message]

 -- * The real work starts here

 -- Store the input dataset
 IF LEN(RTRIM(@tableSpec)) = 0
 BEGIN
   -- Create and populate the temporary table in one step
   SELECT @sql = @sqlSelect + ' INTO ##input ' + @sqlFromWhere
   IF @verbose = 1 SELECT @sql AS [Create and populate the temporary table in one step]
   EXEC sp_executesql @sql
 END
 ELSE
 BEGIN
   -- Create the temporary table first (the only method that can be used if the data is coming from a stored proc)
   SELECT @sql = 'CREATE TABLE ##input (' + @tableSpec + ')'
   IF @verbose = 1 SELECT @sql AS [Create the temporary table]
   EXEC sp_executesql @sql
   -- Populate it
   SELECT @sql = 'INSERT INTO ##input ' + @sqlSelect + ' ' + @sqlFromWhere
   IF @verbose = 1 SELECT @sql AS [Populate the temporary table]
   EXEC sp_executesql @sql
 END
 IF @verbose = 1 SELECT * FROM ##input

 -- Does the query we want to cross-tab already have a column called 'crossTabRowID'? This is a reserved column name!
 IF EXISTS(SELECT 1 FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c WHERE o.id = c.id AND o.name = '##input' AND c.name = 'crossTabRowID')
 BEGIN
   RAISERROR ('The query passed to the [genericCrosstab] procedure uses a reserved column name (''crossTabRowID'').', 16, 1)
   ROLLBACK TRANSACTION
 END

 -- Add an identity column (we need a 'row ID')
 ALTER TABLE ##input ADD crossTabRowID NUMERIC(9, 0) IDENTITY NOT NULL

 -- Generate the column list
 SELECT @sql = 'SELECT ' + @groupBy + ' INTO ##colList FROM ##input WHERE 1 = 0'
 IF @verbose = 1 SELECT @sql AS [Generate the column list]
 EXEC sp_executesql @sql
 IF @verbose = 1 SELECT * FROM ##colList

 -- If no aggregation function is specified, we have to check first that there are no duplicates, and - if there are - we need to handle them
 IF RTRIM(ISNULL(@function, '')) = ''
 BEGIN
   SELECT @sql = 'SELECT ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation + ' AS [pivot], COUNT(1) AS [count] INTO ##grpCount FROM ##input GROUP BY ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation
 END
 ELSE
 BEGIN
   SELECT @sql = 'SELECT ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation + ' AS [pivot], 1 AS [count] INTO ##grpCount FROM ##input GROUP BY ' + @groupBy + ', ' + @pivotExpr + ' COLLATE ' + @collation
 END
 IF @verbose = 1 SELECT @sql AS [Create the ##grpCount table]
 EXEC sp_executesql @sql
 IF @verbose = 1 SELECT * FROM ##grpCount

 -- Create a temporary table that will act as a lookup (containing all of the non-pivot / non-group columns names)
 SELECT [pivot], [count] AS [index], [pivot] AS [column_name] INTO ##lookup FROM ##grpCount WHERE 1 = 0

 -- Build the results table; one row per group
 SELECT @sql = 'SELECT ' + @groupBy + ' INTO ##results FROM ##grpCount GROUP BY ' + @groupBy
 IF @verbose = 1 SELECT @sql AS [Create the ##results table]
 EXEC sp_executesql @sql
 IF @verbose = 1 SELECT * FROM ##results

 -- Build the column list, taking into account duplicate occurences of pivotal values
 DECLARE xcursor CURSOR FOR SELECT [pivot], MAX([count]) FROM ##grpCount GROUP BY [pivot]
 OPEN xcursor

 FETCH NEXT FROM xcursor INTO @pivot, @indx

 WHILE @@FETCH_STATUS = 0
 BEGIN

  SELECT @i = 1

  -- Loop over indx
  WHILE @i <= @indx
  BEGIN

    -- Build the column list
    SELECT @col = CASE @i WHEN 1 THEN @pivot ELSE @pivot + ' (' + CAST(@i AS VARCHAR(10)) + ')' END
    INSERT INTO ##lookup VALUES (@pivot, @i, @col)
    SELECT @col = '[' + @col + '] NVARCHAR(255) NULL'
    SELECT @cols =  ISNULL(@cols + ', ', '') + @col

    -- Add the column to the results table
    SELECT @sql = 'ALTER TABLE ##results ADD ' + @col
    IF @verbose = 1 SELECT @sql AS [Add column to the ##results table]
    EXEC sp_executesql @sql

    -- Continue
    SELECT @i = @i + 1

  END
  
  FETCH NEXT FROM xcursor INTO @pivot, @indx

 END

 CLOSE xcursor
 DEALLOCATE xcursor 

 IF @verbose = 1 SELECT * FROM ##lookup
 IF @verbose = 1 SELECT * FROM ##results

 -- Loop over the column list (using the syscolumns table in the temp database) to build the WHERE clause
 DECLARE xcursor CURSOR FOR SELECT DISTINCT c.[colid], c.[name] FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c WHERE o.[id] = c.[id] AND o.[name] = '##colList' ORDER BY c.[colid]
 OPEN xcursor

 FETCH NEXT FROM xcursor INTO @indx, @col

 WHILE @@FETCH_STATUS = 0
 BEGIN
  
   -- Build the WHERE clause
   SELECT @where = ISNULL(@where + ' AND ', '') + 'ISNULL(t1.[' + @col + '], '''') = ISNULL(t3.[' + @col + '], '''')'
  
   FETCH NEXT FROM xcursor INTO @indx, @col

 END

 CLOSE xcursor
 DEALLOCATE xcursor 

 IF @verbose = 1 SELECT @where AS [WHERE clause]

 -- Create a temp table that will help us build the UPDATE statements to set the values in the pivot table, and the SELECT statement (with columns in preferred order) afterwards
 IF RTRIM(ISNULL(@sortLookup, '')) = ''
 BEGIN

   -- If no sorting table was specified, just select all columns
   SELECT @select = '*'

   -- Sort alphabetically
   SELECT @sql = 'SELECT DISTINCT t5.[index], c.[name], t5.[pivot] INTO ##temp FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c, ##lookup t5 WHERE o.[id] = c.[id] AND o.[name] = ''##results'' AND c.[name] COLLATE ' + @collation + ' = t5.[column_name] COLLATE ' + @collation + ' ORDER BY c.[name], t5.[index]'

 END
 ELSE
 BEGIN

   -- The 'group by' column(s) always come(s) first
   SELECT @select = @groupBy

   -- Sort in proscribed order
   SELECT @sql = 'SELECT DISTINCT t5.[index], c.[name], t5.[pivot], l.[sort_order] INTO ##temp FROM tempdb.dbo.sysobjects o, tempdb.dbo.syscolumns c, ##lookup t5, ' + @sortLookup + ' l WHERE o.[id] = c.[id] AND o.[name] = ''##results'' AND t5.[pivot] COLLATE ' + @collation + ' *= l.[label] COLLATE ' + @collation + ' AND c.[name] COLLATE ' + @collation + ' = t5.[column_name] COLLATE ' + @collation + ' ORDER BY l.[sort_order], c.[name], t5.[index]'

 END

 IF @verbose = 1 SELECT @sql AS [Build the ##temp table]
 EXEC sp_executesql @sql 
 IF @verbose = 1 SELECT * FROM ##temp

 -- The value expression
 SELECT @value = @valueExpr

 -- If a function has been specified
 IF RTRIM(ISNULL(@function, '')) != ''
 BEGIN
   SELECT @value = @function + '(' + @value + ')'
 END

 DECLARE xcursor CURSOR FOR SELECT [index], [name], [pivot] FROM ##temp
 OPEN xcursor

 FETCH NEXT FROM xcursor INTO @indx, @col, @pivot

 WHILE @@FETCH_STATUS = 0
 BEGIN

  -- Build the SELECT expression
  IF @select != '*' SELECT @select = @select + ', ' + @col

  -- Create the SET clause of the UPDATE sql
  IF RTRIM(ISNULL(@function, '')) = ''
  BEGIN
    -- No function specified
    SELECT @update = '[' + @col + '] = (SELECT ' + @value + ' FROM ##input t1 WHERE ' + @where + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + ''' COLLATE ' + @collation + ' AND t1.crossTabRowID = (SELECT MIN(t0.crossTabRowID) FROM ##input t0 WHERE ' + REPLACE(@where, 't3.', 't0.') + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + '''' + ' COLLATE ' + @collation + ') + (' + CAST(@indx AS VARCHAR(3)) + ' - 1))'
  END
  ELSE
  BEGIN
    -- Function specified
    SELECT @update = '[' + @col + '] = (SELECT ' + @value + ' FROM ##input t1 WHERE ' + @where + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + ''' COLLATE ' + @collation + ' AND t1.crossTabRowID IN (SELECT t0.crossTabRowID FROM ##input t0 WHERE ' + REPLACE(@where, 't3.', 't0.') + ' AND ' + @pivotExpr + ' COLLATE ' + @collation + ' = ''' + @pivot + '''' + ' COLLATE ' + @collation + '))'
  END
  SELECT @sql = 'UPDATE ##results SET ' + @update + ' FROM ##results t3'
  IF @verbose = 1 SELECT @sql AS [Create the SET clause of the UPDATE sql]
  EXEC sp_executesql @sql
  
  FETCH NEXT FROM xcursor INTO @indx, @col, @pivot

 END

 CLOSE xcursor
 DEALLOCATE xcursor 

 -- Return the results
 SELECT @sql = 'SELECT ' + @select + ' FROM ##results'
 IF @verbose = 1 SELECT @sql AS [Create the SELECT statement that will return the results]
 EXEC sp_executesql @sql

 -- Tidy up: drop the global temporary tables
 SELECT @sql = N'IF EXISTS (SELECT 1 FROM tempdb.dbo.sysobjects WHERE name = ''@table'' AND xtype = ''U'') DROP TABLE @table'
 SELECT @sqlX = REPLACE(@sql, N'@table', N'##input')
 EXEC sp_executesql @sqlX
 SELECT @sqlX = REPLACE(@sql, N'@table', N'##grpCount')
 EXEC sp_executesql @sqlX
 SELECT @sqlX = REPLACE(@sql, N'@table', N'##colList')
 EXEC sp_executesql @sqlX
 SELECT @sqlX = REPLACE(@sql, N'@table', N'##lookup')
 EXEC sp_executesql @sqlX
 SELECT @sqlX = REPLACE(@sql, N'@table', N'##results')
 EXEC sp_executesql @sqlX
 SELECT @sqlX = REPLACE(@sql, N'@table', N'##temp')
 EXEC sp_executesql @sqlX

END

IF EXISTS (SELECT *
             FROM dbo.sysobjects
            WHERE [id] = object_id(N'[dbo].[testTable]')
              AND OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
  DROP TABLE [dbo].[testTable]
END
GO

CREATE TABLE testTable
 ([who]  VARCHAR(10)  NOT NULL,
  [when] DATETIME     NOT NULL,
  [what] VARCHAR(255) NOT NULL,
  [qty]  INT          NOT NULL)
GO

DELETE FROM testTable
GO

INSERT INTO testTable VALUES ('Fred', '2005-APR-01', 'Coding',  5)
INSERT INTO testTable VALUES ('Jo',   '2005-APR-01', 'Coding',  1)
INSERT INTO testTable VALUES ('Jo',   '2005-APR-01', 'Testing', 7)
INSERT INTO testTable VALUES ('Fred', '2005-APR-01', 'Coding',  3)
INSERT INTO testTable VALUES ('Tim',  '2005-APR-01', 'Coding',  4)
INSERT INTO testTable VALUES ('Fred', '2005-APR-02', 'Coding',  8)
INSERT INTO testTable VALUES ('Jo',   '2005-APR-02', 'Coding',  2)
INSERT INTO testTable VALUES ('Jo',   '2005-APR-02', 'Testing', 6)
INSERT INTO testTable VALUES ('Tim',  '2005-APR-02', 'Coding',  4)
GO

SELECT * FROM testTable
GO

EXEC [dbo].[genericCrosstab]
 @sqlSelect    = 'SELECT *',
 @sqlFromWhere = 'FROM testTable',
 @pivotExpr    = 'what',
 @valueExpr    = 'qty',
 @function     = 'SUM',
 @groupBy      = 'who'
GO

-- Extending the example to proscribe column order...

IF EXISTS (SELECT *
             FROM dbo.sysobjects
            WHERE [id] = object_id(N'[dbo].[testSorter]')
              AND OBJECTPROPERTY(id, N'IsTable') = 1)
BEGIN
  DROP TABLE [dbo].[testSorter]
END
GO

CREATE TABLE testSorter
 ([label]      VARCHAR(255) NOT NULL,
  [sort_order] INT          NOT NULL)
GO

DELETE FROM testSorter
GO

INSERT INTO testSorter VALUES ('Testing',  1)
INSERT INTO testSorter VALUES ('Coding',   2)
GO

SELECT * FROM testSorter
GO

EXEC [dbo].[genericCrosstab]
 @sqlSelect    = 'SELECT *',
 @sqlFromWhere = 'FROM testTable',
 @pivotExpr    = 'what',
 @valueExpr    = 'qty',
 @function     = 'SUM',
 @groupBy      = 'who',
 @sortLookup   = 'testSorter',
 @verbose      = 0
GO

CREATE FUNCTION dbo.f_convert_str_to_date(@str VARCHAR(10))
  RETURNS DATETIME
AS
BEGIN

  -- Declare local variables
  DECLARE @boundary  TINYINT
  DECLARE @separator CHAR(1)
  DECLARE @return    VARCHAR(11)
  DECLARE @index1    INT
  DECLARE @index2    INT
  DECLARE @day       VARCHAR(2)
  DECLARE @month     VARCHAR(2)
  DECLARE @year      VARCHAR(4)
  DECLARE @iDay      TINYINT
  DECLARE @iMonth    TINYINT
  DECLARE @iYear     SMALLINT
  DECLARE @iSwap     TINYINT

  -- 'Constants'
  SELECT @boundary = 20
  SELECT @separator = '/'

  -- Indexes of forward slash separators (2 are expected)
  SELECT @index1 = CHARINDEX(@separator, @str)
  SELECT @index2 = CHARINDEX(@separator, @str, @index1 + 1)

  -- Get the day, month and year
  SELECT @day    = LTRIM(RTRIM(LEFT(@str, @index1 - 1)))
  SELECT @month  = LTRIM(RTRIM(SUBSTRING(@str, (@index1 + 1), (@index2 - @index1) - 1)))
  SELECT @year   = LTRIM(RTRIM(RIGHT(@str, (LEN(@str) - @index2))))

  -- Convert the values to integer representations (will throw an error if they can't be converted)
  SELECT @iDay   = CAST(@day AS TINYINT)
  SELECT @iMonth = CAST(@month AS TINYINT)
  SELECT @iYear  = CAST(@year AS SMALLINT)

  -- Swap the day and month if they're obviously in the wrong format
  IF @iMonth > 12
  BEGIN
    SELECT @iSwap  = @iMonth
    SELECT @iMonth = @iDay
    SELECT @iDay   = @iSwap
  END

  -- Convert back to string representations
  SELECT @day    = CAST(@iDay AS VARCHAR(2))
  SELECT @month  = CAST(@iMonth AS VARCHAR(2))
  SELECT @year   = CAST(@iYear AS VARCHAR(4))

  -- If the day and/or month and/or year are a single digit, prefix with a zero
  SELECT @day    = CASE WHEN LEN(@day)   = 1 THEN '0' + @day   ELSE @day   END
  SELECT @month  = CASE WHEN LEN(@month) = 1 THEN '0' + @month ELSE @month END
  SELECT @year   = CASE WHEN LEN(@year)  = 1 THEN '0' + @year  ELSE @year  END

  -- If the year is only 2 digits long, prefix with '19' or '20' (depending on the boundary)
  IF LEN(@year) = 2 SELECT @year = CASE WHEN @iYear < @boundary THEN '20' + @year ELSE '19' + @year END

  -- Build the cleaned up date string, with the month number converted to a string expression
  SELECT @return = @day + '-'
                 + CASE @month
                     WHEN '01' THEN 'JAN'
                     WHEN '02' THEN 'FEB'
                     WHEN '03' THEN 'MAR'
                     WHEN '04' THEN 'APR'
                     WHEN '05' THEN 'MAY'
                     WHEN '06' THEN 'JUN'
                     WHEN '07' THEN 'JUL'
                     WHEN '08' THEN 'AUG'
                     WHEN '09' THEN 'SEP'
                     WHEN '10' THEN 'OCT'
                     WHEN '11' THEN 'NOV'
                     WHEN '12' THEN 'DEC'
                   END
                 + '-' + @year

  -- Done
  RETURN CAST(@return AS DATETIME)

END

SELECT dbo.f_convert_str_to_date('21/8/01')

SELECT COLUMN_NAME,
       DATA_TYPE,
       CHARACTER_MAXIMUM_LENGTH,
       NUMERIC_PRECISION,
       NUMERIC_SCALE,
       IS_NULLABLE,
       COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IS_AUTOINCREMENT,
       COLUMN_DEFAULT
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = 'xxxxxxxx'
ORDER BY ORDINAL_POSITION

WHILE CHARINDEX('  ', @string) > 0 SELECT @string = REPLACE(@string, '