CODEWITHSUNDEEP
sqlsql-servert-sql
Kalyn
Kalyn

Reputation: 55

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

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:

Upvotes: 1

Views: 2343

Answers (5)

Zhorov
Zhorov

Reputation: 29973

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

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37377

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 :)

Upvotes: 0

Sean Pearce
Sean Pearce

Reputation: 1169

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;

Upvotes: 1

Andrey Nikolov
Andrey Nikolov

Reputation: 13450

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.

Upvotes: 3

SazooCat
SazooCat

Reputation: 170

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

Upvotes: 0

Related Questions