USE tempdb
go
IF db_id('SearchCode') IS NOT NULL
   DROP DATABASE SearchCode
go
CREATE DATABASE SearchCode
go
USE SearchCode
go
CREATE TABLE Numbers (n int NOT NULL PRIMARY KEY);
WITH digits (d) AS (
   SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION
   SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION
   SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION
   SELECT 0)
INSERT Numbers (n)
   SELECT n
   FROM   (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
                  v.d * 10000 + vi.d * 100000 AS n
           FROM   digits i
           CROSS  JOIN digits ii
           CROSS  JOIN digits iii
           CROSS  JOIN digits iv
           CROSS  JOIN digits v
           CROSS  JOIN digits vi) AS Numbers
   WHERE  n > 0
go
CREATE FUNCTION tblnum_split_mstmt(@param nvarchar(MAX), @delim char(1))
RETURNS @t TABLE (Pos   int           NOT NULL PRIMARY KEY,
                  Value nvarchar(MAX) NOT NULL) AS
BEGIN
   INSERT @t (Pos, Value)
      SELECT row_number() OVER(ORDER BY n),
            substring(@param, n,
                        charindex(@delim COLLATE Latin1_General_BIN2,
                                  @param + @delim, n) - n)
       FROM   Numbers
       WHERE  n <= len(@param)
         AND  substring(@delim + @param, n, 1) = @delim COLLATE Latin1_General_BIN2
   RETURN
END
go
CREATE TABLE Objects (object_id int     NOT NULL,
                      schema_   sysname NOT NULL,
                      name      sysname NOT NULL,
                      type      char(2) NOT NULL,
                      CONSTRAINT pk_Objects PRIMARY KEY (object_id),
                      CONSTRAINT u_Objects UNIQUE (schema_, name)
)
go
CREATE TABLE Lines (ident       int IDENTITY   NOT NULL,
                    object_id   int            NOT NULL,
                    linenum     int            NOT NULL,
                    linetext    nvarchar(MAX)  NOT NULL,
                    CONSTRAINT pk_Lines PRIMARY KEY (object_id, linenum),
                    CONSTRAINT u_Lines UNIQUE(ident),
                    CONSTRAINT fk_Lines_Objects FOREIGN KEY(object_id)
                        REFERENCES Objects(object_id)
)
go
-- Replace dependdemo with the name of your database in this and the next query!
INSERT Objects (object_id, schema_, name, type)
   SELECT o.object_id, s.name, o.name, o.type
   FROM   dependdemo.sys.objects o
   JOIN   dependdemo.sys.schemas s ON o.schema_id = s.schema_id
   WHERE  EXISTS (SELECT *
                  FROM   dependdemo.sys.sql_modules sm
                  WHERE  o.object_id = sm.object_id)
go
INSERT Lines (object_id, linenum, linetext)
   SELECT sm.object_id, ism.Pos, ism.Value
   FROM   dependdemo.sys.sql_modules sm
   CROSS  APPLY tblnum_split_mstmt(replace(sm.definition COLLATE Latin1_General_BIN2, char(13), ''), char(10)) ism
   WHERE  EXISTS (SELECT *
                  FROM   dependdemo.sys.objects o
                  WHERE  sm.object_id = o.object_id)
go
-- Create full-text objects.
CREATE FULLTEXT CATALOG fulltext WITH ACCENT_SENSITIVITY = ON AS DEFAULT
CREATE FULLTEXT INDEX ON Lines(linetext LANGUAGE 0x0) KEY INDEX u_Lines
go
-- Use this query to see if the index has been fully populated. When it returns 1,
-- you're ready to go.
SELECT has_crawl_completed FROM sys.fulltext_indexes
go
-- A sample query!
-- Note that the fulltext index is populated asynchronously, so if this
-- query is executed directly after creating the fulltext index it is likely
-- to return no rows.
SELECT O.schema_, O.name, O.type, L.linenum, L.linetext
FROM   Objects O
JOIN   Lines L ON O.object_id = L.object_id
WHERE  CONTAINS(L.linetext, 'tablethree')
ORDER  BY O.name, L.linenum
go

SELECT O.schema_, O.name, O.type, L.linenum, L.linetext
FROM   Objects O
JOIN   Lines L ON O.object_id = L.object_id
WHERE  CONTAINS(L.linetext, '"colone" OR "coltwo"')
ORDER  BY O.name, L.linenum