Find table and column name containing one value-CodePudding

I have limited knowledge of SQL and have access to a SQL Server database in Python3, with no documentation and without user-friendly table and column name descriptions. I'm struggling to find where to look at for the right tables and columns.

I've written a few helper functions using Pandas to get names and tables in the database and to find table or column names containing specific strings:

def find_tables(tablas, s):
        return tablas.loc[(tablas.table_name.str.contains(s, case=False))].drop_duplicates('table_name')


def find_column(tablas, s):
    if isinstance(s, list):
        cond = tablas.column_name.str.contains('|'.join(s))
    else:
        cond = tablas.column_name.str.contains(s, case=False)
        
    return tablas.loc[(cond)]


def explora_tabla(tablas, s):
    return tablas.loc[(tablas.table_name.str.contains(s, case=False))]

Unfortunately, given odd names, this is usually not enough to pull out the information I need. Therefore I though I could try the "brute force way" to find (by value) which table and column names contain a specific value, possibly filtering for other known fields when they are available. Obviously more subtle ways to solve the problem are also welcome.

I have found the following answer, tried it with value 8004YS1LSLR but returns an error I guess because of data type. In any case I would need to be able to match a wider range of formats.

I would like to find a general query that I could pass to pd.read_sql that retuns table and column names in the database containing given value that could be an integer, float, string, etc.

CodePudding user response：

This is yuck, but is based on something I had to write a while ago for a similar problem at the office.

I use a sql_variant here as this means that you can use it to search for other data types, and not have a bunch of implicit conversions in the WHERE. Note, however, that this means it will filter to the underlying data type of the sql_variant; if you supply an nvarchar it won't search varchar, nchar or char columns for example.

As I'm using sql_variant there's some silliness with explicitly conversions as well, which I include the definition of here too.

`QuoteSqlvariant`:

CREATE FUNCTION [fn].[QuoteSqlvariant] (@SQLVariant sql_variant) 
RETURNS nvarchar(258)
AS 
/*
Written by Thom A 2021-03-21
Original Source: https://wp.larnu.uk/sql_variant-and-dynamic-sql/
Licenced under CC BY-ND 4.0
*/
BEGIN
    RETURN QUOTENAME(CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')))  
           CASE WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'char',N'varchar') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'MaxLength')),N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'nchar',N'nvarchar') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'MaxLength'))/2,N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'datetime2',N'datetimeoffset',N'time') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'Scale')),N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'decimal',N'numeric',N'time') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'Precision')),N',',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'Scale')),N')')
                WHEN CONVERT(sysname,SQL_VARIANT_PROPERTY(@SQLVariant,'BaseType')) IN (N'varbinary') THEN CONCAT(N'(',CONVERT(int,SQL_VARIANT_PROPERTY(@SQLVariant,'TotalBytes'))-4,N')')
                ELSE N''
           END;
END
GO

Solution

DECLARE @SearchValue sql_variant = CONVERT(varchar(15),'8004YS1LSLR'); --Explicit Converting is **IMPORTANT** here

DECLARE @CRLF nchar(2) = NCHAR(13)   NCHAR(10),
        @OrDelim nvarchar(10) = NCHAR(13)   NCHAR(10)   N'   OR ',
        @SQL nvarchar(MAX);

WITH ORs AS(
    SELECT s.name AS SchemaName,
           t.name AS TableName,
           STRING_AGG(QUOTENAME(c.[name])   N' = CONVERT('   fn.QuoteSqlvariant(@SearchValue)   N',@SearchValue)', @OrDelim) AS ORClauses
    FROM sys.schemas s
         JOIN sys.tables t ON s.schema_id = t.schema_id
         JOIN sys.columns c ON t.object_id = c.object_id
         JOIN sys.types ct ON c.system_type_id = ct.system_type_id
    WHERE ct.[name] = CONVERT(sysname,SQL_VARIANT_PROPERTY(@SearchValue,'BaseType'))
    GROUP BY s.name,
             t.name)
SELECT @SQL = STRING_AGG(N'SELECT N'   QUOTENAME(SchemaName,'''')   N' AS SchemaName,'   @CRLF  
                         N'       N'   QUOTENAME(TableName,'''')   N' AS TableName,'   @CRLF  
                         N'       *'   @CRLF  
                         N'FROM '   QUOTENAME(SchemaName)   N'.'   QUOTENAME(TableName)   @CRLF  
                         N'WHERE '   OrClauses   N';',@CRLF)
FROM ORs;

EXEC sys.sp_executesql @SQL, N'@SearchValue sql_variant', @SearchValue;

CodePudding user response：

to get the column use the ff code

SELECT name,is_nullable,max_length FROM sys.columns WHERE object_id = OBJECT_ID('yourtablename')

QuoteSqlvariant:

Solution

`QuoteSqlvariant`: