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'
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.)