SQL Server to check all columns in a databases views for a specific string value

Question

My employer has a third party application that has a very complex set of obliquely named views. I am trying to locate the views that contain specific data that is entered in the applications UI.

I have SQL to build a CTE with all view names and column names...

What I can't figure out is how to test every column (that could hold a string value) in every view for a specific string value.

Here is the start I made which doesn't work, but will illustrate what I am trying to do. Please note that it is also missing a constraint on the columns to check based on what can hold a string value.

The other obvious problem with the below is that I want to evaluate the value of the column and NOT the column name for value.

WITH ViewColumn_CTE (ViewName, ColumnName) AS
(
    SELECT TOP 100
        V.Name as ViewName, 
        C.Name as ColumnName
    FROM
        sys.views V 
    JOIN
        SysColumns C ON V.Object_ID = C.ID
)
SELECT TOP 1
    ViewName,
    ColumnName
FROM
    ViewColumn_CTE
WHERE
    ColumnName = 'Cash Equivalents'

Where do you have the view definition in your CTE? Most (if not all) database system offer you the definition in their system tables somewhere, but you will have to read the documentation or consult with your DBA what the name of these is. Please also [edit] your post and add the SQL server product that you are using as a tag. — Corion, Jan 17 '19 at 18:37
I edited the tags to include SQL server 2014. Sys.views is only returning the view names, I am not seeing any useful definition information there. SysColumns would seem to have datatype information but it isn't jumping out at me. I can see field length, nullability, etc... when I select * from it. — MWest, Jan 17 '19 at 19:39
This is a bad idea since N number of views can reference a single table where the data is *actually* stored. Views are just *virtual* tables that are defined by a query. What you should care about is what *tables* the data is stored in. From that, you can find what *views* reference these *tables* if you really care. Regardless, searching every row for every table (or view) that has a `varchar` value sounds horrible. What would be better is to use SQL Profiler or another tool to trace what query is being called when the user enters the data. Then, you'd know the tables. — S3S, Jan 17 '19 at 19:51

sticky bit · Accepted Answer · 2019-01-17T21:49:25.237

I have such a script flying around here. You can use it for a start.

It gets the list of interesting columns from the catalog iterates over them and queries them using dynamic SQL.

DECLARE @searched_value nvarchar(MAX) = 'a'; -- set to the value you search for

SET NOCOUNT ON;

DECLARE @schema_name sysname;
DECLARE @table_name sysname;
DECLARE @column_name sysname;
DECLARE @sql nvarchar(MAX);
DECLARE @result TABLE ([schema_name] sysname,
                       [table_name] sysname,
                       [column_name] sysname,
                       [value] nvarchar(MAX));

DECLARE cursor_all_columns CURSOR
                           LOCAL
                           FAST_FORWARD
FOR
SELECT s.name,
       o.name,
       c.name
       FROM sys.schemas s
            INNER JOIN sys.all_objects o
                       ON o.schema_id = s.schema_id
            INNER JOIN sys.all_columns c
                       ON c.object_id = o.object_id
            INNER JOIN sys.types y
                       ON y.user_type_id = c.user_type_id
       WHERE o.type = 'U' -- set to 'V' for views
             AND lower(y.name) IN ('char',
                                   'nchar',
                                   'varchar',
                                   'nvarchar'); -- include more types if needed

OPEN cursor_all_columns;
FETCH NEXT FROM cursor_all_columns
           INTO @schema_name,
                @table_name,
                @column_name;
WHILE @@fetch_status = 0
BEGIN
  SET @sql =   N'SELECT ''' + quotename(@schema_name) + N''',' + nchar(13) + nchar(10)
             + N'       ''' + quotename(@table_name) + N''',' + nchar(13) + nchar(10)
             + N'       ''' + quotename(@column_name) + N''',' + nchar(13) + nchar(10)
             + N'       ' + quotename(@column_name) + N'' + nchar(13) + nchar(10)
             + N'       FROM ' + quotename(@schema_name) + N'.' + quotename(@table_name) + N'' + nchar(13) + nchar(10)
             + N'       WHERE lower(' + quotename(@column_name) + N') LIKE N''%' + lower(replace(replace(replace(@searched_value, '%', '!%'), '[', '!['), ']', '!]')) + N'%'' ESCAPE ''!'';' + nchar(13) + nchar(10);

  INSERT INTO @result
              EXEC sp_executesql @sql;

  FETCH NEXT FROM cursor_all_columns
             INTO @schema_name,
                  @table_name,
                  @column_name;
END;
CLOSE cursor_all_columns;
DEALLOCATE cursor_all_columns;

SELECT [schema_name],
       [table_name],
       [column_name],
       [value]
       FROM @result;

Is is meant for tables, but it should also work for views, if you change the object type from 'U' to 'V'. (Though the tables might be more interesting thing anyway.) No guarantees though.

Set the value you search for at the beginning.

It searches for any column of the (n)(var)char type if it contains the searched value, case insensitive. If you want any custom types derived from string types included you have to adapt it accordingly.

The output is the schema, the table (or view) name, the column name and the value, that matched (a column might be listed more than once if multiple rows contain the searched value in that column).

(Disclaimer: Might have room for improvement or bugs.)

SQL Server to check all columns in a databases views for a specific string value

1 Answers1