How To Search All Columns With Multiple Words

Jump To Right Section Show

In this post, we want to discuss a nice function to convert text to title case (Proper Case) in SQL Server. Before we get started, if you want to know about splitting with any delimited string, please go through the following article: Search string value in all columns of a table.

When reviewing that beyond the relational blog post we had an important comment about that blog. The comment was a question about How to search all columns with Multiple Words. For this problem let’s assume SQL Server 2008 and up, so we will be describing TVP (table-valued parameter) solution.

Let’s create a similar stored procedure that will work with the list of words to search in every character column in a table. First, we need to create a table type and then do the following modifications to the procedure:

Search All Columns with Multiple Words


IF TYPE_ID(N'SearchWords') IS NULL
    CREATE TYPE SearchWords AS TABLE
( Word nvarchar(50) PRIMARY KEY );
GO
 
IF NOT EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE ROUTINE_NAME = 'spSearchWordsInTable'
            AND ROUTINE_TYPE = 'PROCEDURE'
        )
    EXECUTE ('CREATE PROCEDURE dbo.spSearchWordsInTable AS SET NOCOUNT ON;');
GO
  
ALTER PROCEDURE dbo.spSearchWordsInTable
(@SearchWords SearchWords READONLY,
 @Table_Schema sysname = 'dbo',
 @Table_Name sysname)
 AS
 BEGIN
DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX), @PkColumn NVARCHAR(MAX)
  
-- Get all character columns
SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Column_Name)
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
 AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema
 ORDER BY COLUMN_NAME
 FOR XML PATH('')),1,2,'')
  
IF @Columns IS NULL -- no character columns
   RETURN -1
  
-- Get columns for select statement - we need to convert all columns to nvarchar(max)
SET @Cols = STUFF((SELECT ', CAST(' + QUOTENAME(Column_Name) + ' as nvarchar(max)) COLLATE DATABASE_DEFAULT AS ' + QUOTENAME(Column_Name)
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
 AND TABLE_NAME = @Table_Name
 AND TABLE_SCHEMA = @Table_Schema
 ORDER BY COLUMN_NAME
 FOR XML PATH('')),1,2,'')
   
 SET @PkColumn = STUFF((SELECT N' + ''|'' + ' + ' CAST(' + QUOTENAME(CU.COLUMN_NAME) + ' as nvarchar(max)) COLLATE DATABASE_DEFAULT'
  
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
  
 WHERE TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME = @Table_Name
 ORDER BY CU.ORDINAL_POSITION
 FOR XML PATH('')),1,9,'')
  
 IF @PkColumn IS NULL
    SELECT @PkColumn = 'CAST(NULL as nvarchar(max))'
      
 -- set select statement using dynamic UNPIVOT
 DECLARE @SQL NVARCHAR(MAX)
 SET @SQL = 'SELECT unpvt.*, ' + QUOTENAME(@Table_Schema,'''') + ' AS [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' AS [Table Name]' +
  ' FROM
  (SELECT '+ @PkColumn + ' as [PK Column], ' + @Cols + ' FROM ' + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name) +  ') src UNPIVOT ([Column Value] FOR [Column Name] IN (' + @Columns + ')) unpvt
 WHERE EXISTS (SELECT 1 FROM @SearchWords SW WHERE unpvt.[Column Value] LIKE ''%'' + SW.Word + ''%'')'
   
 --print @SQL
  
EXECUTE sp_ExecuteSQL @SQL, N'@SearchWords SearchWords READONLY', @SearchWords
END
GO
And to test this new procedure let's try:

DECLARE @SWords AS SearchWords;
INSERT INTO @SWords (Word) VALUES ('Test'), ('Reserved');
 
execute dbo.spSearchWordsInTable @SearchWords = @SWords, @table_schema = 'dbo', @table_name = 'items'
We can also test this procedure to get information for all tables in the database:

DECLARE @SWords AS SearchWords;
INSERT INTO @SWords (Word) VALUES ('Test'), ('Reserved');
 
--execute dbo.spSearchWordsInTable @SearchWords = @SWords, @table_schema = 'Sales', @table_name = 'Store'
 
IF OBJECT_ID('TempDB..#Result', N'U') IS NOT NULL DROP TABLE #Result;
CREATE TABLE #RESULT ([PK COLUMN] NVARCHAR(MAX), [COLUMN VALUE] NVARCHAR(MAX), [COLUMN Name] sysname, [TABLE SCHEMA] sysname, [TABLE Name] sysname)
DECLARE @Table_Name sysname, @SearchString NVARCHAR(MAX), @Table_Schema sysname
 
 
DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
    FOR
    SELECT   Table_Schema, Table_Name
    FROM     INFORMATION_SCHEMA.Tables   
    WHERE TABLE_TYPE = 'BASE TABLE'
    ORDER BY Table_Schema, Table_Name
     
    OPEN curAllTables
    FETCH  curAllTables
    INTO @Table_Schema, @Table_Name   
    WHILE (@@FETCH_STATUS = 0) -- Loop through all tables in the database
      BEGIN
        INSERT #RESULT
        EXECUTE dbo.spSearchWordsInTable @SearchWords = @SWords, @Table_Schema = @Table_Schema, @Table_Name = @Table_Name
     
        FETCH  curAllTables
        INTO @Table_Schema, @Table_Name
      END -- while
    CLOSE curAllTables
    DEALLOCATE curAllTables
  -- Return results
  SELECT * FROM #RESULT ORDER BY [Table Name]

100

101

102

103

104

105

106

107

108

109

110

111

IF TYPE_ID(N'SearchWords') IS NULL

CREATE TYPE SearchWords AS TABLE

( Word nvarchar(50) PRIMARY KEY );

IF NOT EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME = 'spSearchWordsInTable'

AND ROUTINE_TYPE = 'PROCEDURE'

)

EXECUTE ('CREATE PROCEDURE dbo.spSearchWordsInTable AS SET NOCOUNT ON;');

ALTER PROCEDURE dbo.spSearchWordsInTable

(@SearchWords SearchWords READONLY,

@Table_Schema sysname = 'dbo',

@Table_Name sysname)

BEGIN

DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX), @PkColumn NVARCHAR(MAX)

-- Get all character columns

SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Column_Name)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')

AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema

ORDER BY COLUMN_NAME

FOR XML PATH('')),1,2,'')

IF @Columns IS NULL -- no character columns

RETURN -1

-- Get columns for select statement - we need to convert all columns to nvarchar(max)

SET @Cols = STUFF((SELECT ', CAST(' + QUOTENAME(Column_Name) + ' as nvarchar(max)) COLLATE DATABASE_DEFAULT AS ' + QUOTENAME(Column_Name)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')

AND TABLE_NAME = @Table_Name

AND TABLE_SCHEMA = @Table_Schema

ORDER BY COLUMN_NAME

FOR XML PATH('')),1,2,'')

SET @PkColumn = STUFF((SELECT N' + ''|'' + ' + ' CAST(' + QUOTENAME(CU.COLUMN_NAME) + ' as nvarchar(max)) COLLATE DATABASE_DEFAULT'

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC

INNER JOIN

INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU

ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND

TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME

WHERE TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME = @Table_Name

ORDER BY CU.ORDINAL_POSITION

FOR XML PATH('')),1,9,'')

IF @PkColumn IS NULL

SELECT @PkColumn = 'CAST(NULL as nvarchar(max))'

-- set select statement using dynamic UNPIVOT

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'SELECT unpvt.*, ' + QUOTENAME(@Table_Schema,'''') + ' AS [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' AS [Table Name]' +

' FROM

(SELECT '+ @PkColumn + ' as [PK Column], ' + @Cols + ' FROM ' + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name) + ') src UNPIVOT ([Column Value] FOR [Column Name] IN (' + @Columns + ')) unpvt

WHERE EXISTS (SELECT 1 FROM @SearchWords SW WHERE unpvt.[Column Value] LIKE ''%'' + SW.Word + ''%'')'

--print @SQL

EXECUTE sp_ExecuteSQL @SQL, N'@SearchWords SearchWords READONLY', @SearchWords

END

And to test this new procedure let's try:

DECLARE @SWords AS SearchWords;

INSERT INTO @SWords (Word) VALUES ('Test'), ('Reserved');

execute dbo.spSearchWordsInTable @SearchWords = @SWords, @table_schema = 'dbo', @table_name = 'items'

We can also test this procedure to get information for all tables in the database:

DECLARE @SWords AS SearchWords;

INSERT INTO @SWords (Word) VALUES ('Test'), ('Reserved');

--execute dbo.spSearchWordsInTable @SearchWords = @SWords, @table_schema = 'Sales', @table_name = 'Store'

IF OBJECT_ID('TempDB..#Result', N'U') IS NOT NULL DROP TABLE #Result;

CREATE TABLE #RESULT ([PK COLUMN] NVARCHAR(MAX), [COLUMN VALUE] NVARCHAR(MAX), [COLUMN Name] sysname, [TABLE SCHEMA] sysname, [TABLE Name] sysname)

DECLARE @Table_Name sysname, @SearchString NVARCHAR(MAX), @Table_Schema sysname

DECLARE curAllTables CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY

FOR

SELECT Table_Schema, Table_Name

FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_TYPE = 'BASE TABLE'

ORDER BY Table_Schema, Table_Name

OPEN curAllTables

FETCH curAllTables

INTO @Table_Schema, @Table_Name

WHILE (@@FETCH_STATUS = 0) -- Loop through all tables in the database

BEGIN

INSERT #RESULT

EXECUTE dbo.spSearchWordsInTable @SearchWords = @SWords, @Table_Schema = @Table_Schema, @Table_Name = @Table_Name

FETCH curAllTables

INTO @Table_Schema, @Table_Name

END -- while

CLOSE curAllTables

DEALLOCATE curAllTables

-- Return results

SELECT * FROM #RESULT ORDER BY [Table Name]

Search All Columns in a Table for all Passed Words


IF TYPE_ID(N'SearchWords') IS NULL
    CREATE TYPE SearchWords AS TABLE
( Word nvarchar(50) PRIMARY KEY );
GO
 
IF NOT EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE ROUTINE_NAME = 'spSearchWordsInTableInclusive'
            AND ROUTINE_TYPE = 'PROCEDURE'
        )
    EXECUTE ('CREATE PROCEDURE dbo.spSearchWordsInTableInclusive AS SET NOCOUNT ON;');
GO
  
ALTER PROCEDURE spSearchWordsInTableInclusive
(@SearchWords SearchWords READONLY,
 @Table_Schema sysname = 'dbo',
 @Table_Name sysname)
 AS
 BEGIN
DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX), @PkColumn NVARCHAR(MAX)
  
-- Get all character columns
SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Column_Name)
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
 AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema
 ORDER BY COLUMN_NAME
 FOR XML PATH('')),1,2,'');
  
IF @Columns IS NULL -- no character columns
   RETURN -1
  
-- Get columns for select statement - we need to convert all columns to nvarchar(max)
SET @Cols = STUFF((SELECT ', CAST(' + QUOTENAME(Column_Name) + ' AS nvarchar(max)) COLLATE DATABASE_DEFAULT AS ' + QUOTENAME(Column_Name)
 FROM INFORMATION_SCHEMA.COLUMNS
 WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')
 AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema
 ORDER BY COLUMN_NAME
 FOR XML PATH('')),1,2,'')
   
 SET @PkColumn = STUFF((SELECT N' + ''|'' + ' + ' CAST(' + QUOTENAME(CU.COLUMN_NAME) + ' AS nvarchar(max)) COLLATE DATABASE_DEFAULT'
  
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
  
 WHERE TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME = @Table_Name
 ORDER BY CU.ORDINAL_POSITION
 FOR XML PATH('')),1,9,'')
  
 IF @PkColumn IS NULL
    SELECT @PkColumn = 'CAST(NULL as nvarchar(max))'
      
 -- set select statement using dynamic UNPIVOT
 DECLARE @SQL NVARCHAR(MAX)
 SET @SQL = 'DECLARE @NumberOfWords INT ;
 SELECT @NumberOfWords = COUNT(*) FROM @SearchWords;
 
 SELECT unpvt.*, ' + QUOTENAME(@Table_Schema,'''') + ' AS [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' AS [Table Name]' +
  ' FROM
  (SELECT '+ @PkColumn + ' as [PK Column], ' + @Cols + ' FROM ' + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name) +  ') src UNPIVOT ([Column Value] for [Column Name] IN (' + @Columns + ')) unpvt
INNER JOIN @SearchWords SW ON unpvt.[Column Value] LIKE ''%'' + SW.Word + ''%''
GROUP BY unpvt.[PK Column], unpvt.[Column Value], unpvt.[Column Name]
HAVING COUNT(DISTINCT(Sw.Word)) = @NumberOfWords'
   
 --print @SQL
  
EXECUTE sp_ExecuteSQL @SQL, N'@SearchWords SearchWords READONLY', @SearchWords
END
GO
and to test:

DECLARE @SWords AS SearchWords;
INSERT INTO @SWords (Word) VALUES ('Test'), ('Item');

IF TYPE_ID(N'SearchWords') IS NULL

CREATE TYPE SearchWords AS TABLE

( Word nvarchar(50) PRIMARY KEY );

IF NOT EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME = 'spSearchWordsInTableInclusive'

AND ROUTINE_TYPE = 'PROCEDURE'

)

EXECUTE ('CREATE PROCEDURE dbo.spSearchWordsInTableInclusive AS SET NOCOUNT ON;');

ALTER PROCEDURE spSearchWordsInTableInclusive

(@SearchWords SearchWords READONLY,

@Table_Schema sysname = 'dbo',

@Table_Name sysname)

BEGIN

DECLARE @Columns NVARCHAR(MAX), @Cols NVARCHAR(MAX), @PkColumn NVARCHAR(MAX)

-- Get all character columns

SET @Columns = STUFF((SELECT ', ' + QUOTENAME(Column_Name)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')

AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema

ORDER BY COLUMN_NAME

FOR XML PATH('')),1,2,'');

IF @Columns IS NULL -- no character columns

RETURN -1

-- Get columns for select statement - we need to convert all columns to nvarchar(max)

SET @Cols = STUFF((SELECT ', CAST(' + QUOTENAME(Column_Name) + ' AS nvarchar(max)) COLLATE DATABASE_DEFAULT AS ' + QUOTENAME(Column_Name)

FROM INFORMATION_SCHEMA.COLUMNS

WHERE DATA_TYPE IN ('text','ntext','varchar','nvarchar','char','nchar')

AND TABLE_NAME = @Table_Name AND TABLE_SCHEMA = @Table_Schema

ORDER BY COLUMN_NAME

FOR XML PATH('')),1,2,'')

SET @PkColumn = STUFF((SELECT N' + ''|'' + ' + ' CAST(' + QUOTENAME(CU.COLUMN_NAME) + ' AS nvarchar(max)) COLLATE DATABASE_DEFAULT'

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC

INNER JOIN

INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU

ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND

TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME

WHERE TC.TABLE_SCHEMA = @Table_Schema AND TC.TABLE_NAME = @Table_Name

ORDER BY CU.ORDINAL_POSITION

FOR XML PATH('')),1,9,'')

IF @PkColumn IS NULL

SELECT @PkColumn = 'CAST(NULL as nvarchar(max))'

-- set select statement using dynamic UNPIVOT

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'DECLARE @NumberOfWords INT ;

SELECT @NumberOfWords = COUNT(*) FROM @SearchWords;

SELECT unpvt.*, ' + QUOTENAME(@Table_Schema,'''') + ' AS [Table Schema], ' + QUOTENAME(@Table_Name,'''') + ' AS [Table Name]' +

' FROM

(SELECT '+ @PkColumn + ' as [PK Column], ' + @Cols + ' FROM ' + QUOTENAME(@Table_Schema) + '.' + QUOTENAME(@Table_Name) + ') src UNPIVOT ([Column Value] for [Column Name] IN (' + @Columns + ')) unpvt

INNER JOIN @SearchWords SW ON unpvt.[Column Value] LIKE ''%'' + SW.Word + ''%''

GROUP BY unpvt.[PK Column], unpvt.[Column Value], unpvt.[Column Name]

HAVING COUNT(DISTINCT(Sw.Word)) = @NumberOfWords'

--print @SQL

EXECUTE sp_ExecuteSQL @SQL, N'@SearchWords SearchWords READONLY', @SearchWords

END

and to test:

DECLARE @SWords AS SearchWords;

INSERT INTO @SWords (Word) VALUES ('Test'), ('Item');

To test this procedure you can pass string name to search, table schema, and table name like so:


execute dbo.spSearchWordsInTableInclusive @SearchWords = @SWords, @table_schema = 'dbo', @table_name = 'items'

execute dbo.spSearchWordsInTableInclusive @SearchWords = @SWords, @table_schema = 'dbo', @table_name = 'items'

Searching Specific Columns in all Tables of a Database

Finally, let’s consider the problem mentioned at the beginning of this article with the MSDN forum’s post reference which prompted this article. Let’s assume we know the column’s name but we don’t know the table’s name. The solution will be similar to what we have been using before:


IF NOT EXISTS (
        SELECT *
        FROM INFORMATION_SCHEMA.ROUTINES
        WHERE ROUTINE_NAME = 'spSearchAllTablesColumnName'
            AND ROUTINE_TYPE = 'PROCEDURE'
        )
    EXECUTE ('CREATE PROCEDURE dbo.spSearchAllTablesColumnName AS SET NOCOUNT ON;');
GO
  
ALTER PROCEDURE spSearchAllTablesColumnName (
    @ColumnName SYSNAME
    ,@SearchString NVARCHAR(max)
    )
AS
DECLARE @Tables TABLE (
    TableName SYSNAME
    ,TableSchema SYSNAME
    ,PKColumn NVARCHAR(max)
    )
  
INSERT INTO @Tables (
    TableName
    ,TableSchema
    ,PKColumn
    )
SELECT Table_name
    ,table_schema
    ,COALESCE(STUFF((
                SELECT N' + ''|'' + ' + ' cast(' + QUOTENAME(CU.COLUMN_NAME) + ' as nvarchar(max))'
                FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
   
 WHERE TC.TABLE_SCHEMA = C.Table_Schema AND TC.TABLE_NAME = C.Table_Name
 ORDER BY CU.ORDINAL_POSITION
 FOR XML PATH('')),1,9,''), 'CAST(NULL as nvarchar(max))')
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE COLUMN_NAME = @ColumnName
    AND DATA_TYPE IN (
        'text'
        ,'ntext'
        ,'varchar'
        ,'nvarchar'
        ,'char'
        ,'nchar'
        );
  
IF @@ROWCOUNT = 0
BEGIN
    DECLARE @dbName SYSNAME;
  
    SET @dbName = QUOTENAME(DB_NAME());
  
    RAISERROR (
            'No tables have [%s] column in the database %s'
            ,16
            ,1
            ,@ColumnName
            ,@dbName
            );
  
    RETURN - 1;
END
  
DECLARE @SQL NVARCHAR(max);
  
SELECT @SQL = STUFF((
            SELECT 'UNION ALL
SELECT ' + PKColumn + ' AS PK, ' + quotename(TableSchema, '''') + ' AS TableSchema, ' + quotename(TableName, '''') +
' AS TableName, ' + quotename(@ColumnName) + ' AS ' +
 quotename(@ColumnName) + ' FROM ' + quotename(TableSchema) + '.' + quotename(TableName) +
 ' WHERE ' + quotename(@ColumnName) + ' LIKE ''%' + @SearchString + '%'' '
            FROM @Tables
            ORDER BY TableSchema
                ,TableName
            FOR XML PATH('')
                ,type
            ).value('.', 'nvarchar(max)'), 1, 10, '')
  
--PRINT @SQL
  
EXECUTE sp_executeSQL @SQL
    ,N'@SearchString nvarchar(max)'
    ,@SearchString
GO
And we can test this new procedure this way:

EXECUTE spSearchAllTablesColumnName @ColumnName = 'descrip'
    ,@SearchString = N'test';

IF NOT EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME = 'spSearchAllTablesColumnName'

AND ROUTINE_TYPE = 'PROCEDURE'

)

EXECUTE ('CREATE PROCEDURE dbo.spSearchAllTablesColumnName AS SET NOCOUNT ON;');

ALTER PROCEDURE spSearchAllTablesColumnName (

@ColumnName SYSNAME

,@SearchString NVARCHAR(max)

)

DECLARE @Tables TABLE (

TableName SYSNAME

,TableSchema SYSNAME

,PKColumn NVARCHAR(max)

)

INSERT INTO @Tables (

TableName

,TableSchema

,PKColumn

)

SELECT Table_name

,table_schema

,COALESCE(STUFF((

SELECT N' + ''|'' + ' + ' cast(' + QUOTENAME(CU.COLUMN_NAME) + ' as nvarchar(max))'

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC

INNER JOIN

INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU

ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND

TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME

WHERE TC.TABLE_SCHEMA = C.Table_Schema AND TC.TABLE_NAME = C.Table_Name

ORDER BY CU.ORDINAL_POSITION

FOR XML PATH('')),1,9,''), 'CAST(NULL as nvarchar(max))')

FROM INFORMATION_SCHEMA.COLUMNS C

WHERE COLUMN_NAME = @ColumnName

AND DATA_TYPE IN (

'text'

,'ntext'

,'varchar'

,'nvarchar'

,'char'

,'nchar'

);

IF @@ROWCOUNT = 0

BEGIN

DECLARE @dbName SYSNAME;

SET @dbName = QUOTENAME(DB_NAME());

RAISERROR (

'No tables have [%s] column in the database %s'

,16

,@ColumnName

,@dbName

);

RETURN - 1;

END

DECLARE @SQL NVARCHAR(max);

SELECT @SQL = STUFF((

SELECT 'UNION ALL

SELECT ' + PKColumn + ' AS PK, ' + quotename(TableSchema, '''') + ' AS TableSchema, ' + quotename(TableName, '''') +

' AS TableName, ' + quotename(@ColumnName) + ' AS ' +

quotename(@ColumnName) + ' FROM ' + quotename(TableSchema) + '.' + quotename(TableName) +

' WHERE ' + quotename(@ColumnName) + ' LIKE ''%' + @SearchString + '%'' '

FROM @Tables

ORDER BY TableSchema

,TableName

FOR XML PATH('')

,type

).value('.', 'nvarchar(max)'), 1, 10, '')

--PRINT @SQL

EXECUTE sp_executeSQL @SQL

,N'@SearchString nvarchar(max)'

,@SearchString

And we can test this new procedure this way:

EXECUTE spSearchAllTablesColumnName @ColumnName = 'descrip'

,@SearchString = N'test';

The article was published on May 30, 2023 @ 9:43 PM

How to search all columns with multiple words

Search All Columns with Multiple Words

Search All Columns in a Table for all Passed Words

Searching Specific Columns in all Tables of a Database

Leave a Comment X

How to search all columns with multiple words

Search All Columns with Multiple Words

Search All Columns in a Table for all Passed Words

Searching Specific Columns in all Tables of a Database

You may also like

Leave a Comment X