how to find tables that have column values if i don't know the table

Question

We have an application used at work that was created by a third party. So we have access to the UI and not the backend code. On this application there is a dropdown list. I do have access to the MSSQL DB that this application runs on.

I need to know where this dropdown list gets its info from but I don't know which table it uses (because I cant check the code directly).

Is there a query I can write to search the entire DB for tables that have columns that contain a specific value ?

The dropdownlist values are:

WebsiteAPP
BOT App
Walkin
BotLead

score 3 · Answer 1 · answered Jan 10 '19 at 09:32

3

You can use SQL Server Profiler or Extended Events session to capture the executed statements against the database. The easiest way to do this is to use the SSMS XEvent Profiler feature (introduced in Management Studio version 17.3). In essence, you start to capture the executed statements, open the application to make it load the list, stop the tracing and look at the captured queries to determine the source of the data.

answered Jan 10 '19 at 09:32

Andrey Nikolov

12,967
3
20
32

The problem SQL Server Profiler is there are thousands of people using the app daily. locating that exact query will be a mission – Kalyn Jan 10 '19 at 09:41
You can do this on separate test instance, or in maintenance window. But if you can't, then you can search the contents of all text columns in all tables. There are many questions in SO, for example [this one](https://stackoverflow.com/questions/15757263/find-a-string-by-searching-all-tables-in-sql-server-management-studio-2008). – Andrey Nikolov Jan 10 '19 at 09:49

score 1 · Answer 2 · answered Jan 10 '19 at 09:55

USE AdventureWorks;
GO

SET NOCOUNT ON;

DECLARE @T TABLE (ColumnName SYSNAME, TableName SYSNAME);

DECLARE
    @SearchString VARCHAR(255) = 'Simon',
    @SQL NVARCHAR(MAX);

DECLARE CUR CURSOR LOCAL FAST_FORWARD FOR
SELECT
    'SELECT ' + 
        QUOTENAME(c.name) + ', ''' + 
        OBJECT_SCHEMA_NAME(c.object_id) + '.' + OBJECT_NAME(c.object_id) + 
    ''' FROM ' + QUOTENAME(OBJECT_SCHEMA_NAME(c.object_id)) + '.' + QUOTENAME(OBJECT_NAME(c.object_id)) + 
    ' WHERE ' + QUOTENAME(c.name) + ' = ''' + @SearchString + ''';'
FROM
    sys.columns c
INNER JOIN
    sys.types t
        ON c.user_type_id = t.user_type_id
WHERE
    c.collation_name IS NOT NULL
    AND t.name NOT IN ('text','ntext')
    AND OBJECTPROPERTY(c.object_id,'IsSystemTable') = 0
    AND OBJECTPROPERTY(c.object_id,'IsMSShipped') = 0
    AND OBJECTPROPERTY(c.object_id,'IsTable') = 1;

OPEN CUR;
FETCH CUR INTO @SQL;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @SQL;

    INSERT INTO @T
    EXEC sp_executesql @SQL;

    FETCH CUR INTO @SQL;
END;
CLOSE CUR;
DEALLOCATE CUR;

SELECT DISTINCT
    *
FROM
    @T;

Zhorov · Answer 3 · 2019-01-10T11:24:51.203

You may try to generate and execute a dynamic SQL statement, based on the information in INFORMATION_SCHEMA.COLUMNS:

DECLARE @stm nvarchar(max)
SET @stm = N''

SELECT @stm = @stm +
    N'SELECT ' +
    N'''' + TABLE_NAME + N''' AS TableName, ' +
    N'''' + COLUMN_NAME + N''' AS ColumnName, ' +
    QUOTENAME(COLUMN_NAME) + 
    N' FROM ' + QUOTENAME(TABLE_NAME) +
    N' WHERE ' + QUOTENAME(COLUMN_NAME) + N' IN (''WebsiteAPP'', ''BOT App'', ''Walkin'', ''BotLead''); '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
    DATA_TYPE = ('varchar') OR 
    DATA_TYPE = ('nvarchar');

PRINT @stm
EXEC sp_executesql @stm

Or just get all SQL statements in a table and execute them as you want:

SELECT
    N'SELECT ' +
    N'''' + TABLE_NAME + N''' AS TableName, ' +
    N'''' + COLUMN_NAME + N''' AS ColumnName, ' +
    QUOTENAME(COLUMN_NAME) + 
    N' FROM ' + QUOTENAME(TABLE_NAME) +
    N' WHERE ' + QUOTENAME(COLUMN_NAME) + N' IN (''WebsiteAPP'', ''BOT App'', ''Walkin'', ''BotLead''); '
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
    DATA_TYPE = ('varchar') OR 
    DATA_TYPE = ('nvarchar');

score 0 · Answer 4 · answered Jan 10 '19 at 09:28

0

You could query sys.tables and sys.columns to locate a list of table.column that contain text information, then from that generate a number of sql queries select [columnname] from [tablename] where [columnname] in ('value1','value2')

answered Jan 10 '19 at 09:28

SazooCat

150
1
6

Michał Turczyn · Answer 5 · 2019-01-10T10:56:29.737

You can achieve what you want with following query, just replace MyDatabase with name of your database (necessary comments in code):

-- define cursor to loop through all columns
declare crs cursor for
select object_name(object_id), name from MyDatabase.sys.columns
where collation_name is not null --here we add this constraint to check only varchar columns
declare @tableName varchar(1000), @columnName varchar(1000), @sql varchar(1000);

open crs
fetch next from crs into @tableName, @columnName
while @@FETCH_STATUS = 0
begin
    -- craete dynamic sql, which will check if all values exist in that column, if so, print table name
    set @sql = 'if exists(select * from MyDatabase..' + @tableName + ' where ' + @columnName + ' = ''Value1'') ' +
    -- to add more values, just modify this dynamic sql (add another exist(...))
        'and exists(select * from MyDatabase..' + @tableName + ' where ' + @columnName + ' = ''Value2'') '
        'and exists(select * from MyDatabase..' + @tableName + ' where ' + @columnName + ' = ''Value3'') print ''' + @tableName + ''';'
    exec (@sql)
    fetch next from crs into @tableName, @columnName
end
close crs
deallocate crs

NOTE: it will generate couple of errors, but among them there will be table name printed, just look it up :)

how to find tables that have column values if i don't know the table

5 Answers5