在sql数据库中搜索列名，然后在重新调整的列中搜索值

Question

该查询将在数据库中搜索特定的列名。我想更进一步，并在返回的列中搜索特定值。

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name,
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%Example%'

有任何想法吗？

非常感谢

Answer 1

没有此类搜索的系统表。鉴于您可以尝试此目的

DECLARE @ValueToSearch NVARCHAR(500)
DECLARE @SearchColumn NVARCHAR(100)
DECLARE @TableName NVARCHAR(200)
DECLARE @ColumnName NVARCHAR(200)
SET @ValueToSearch ='YOUR VALUE TP SEARCH'
SET @SearchColumn = 'YOUR COLUMN'
DECLARE @getResult CURSOR
SET @getResult = CURSOR FOR
    SELECT t.name AS table_name,c.name AS column_name FROM sys.tables AS t INNER JOIN     sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name = @SearchColumn
    OPEN @getResult
    FETCH NEXT FROM @getResult INTO @TableName,@ColumnName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET NOCOUNT ON ;
        DECLARE @RESULT INT; 
        DECLARE @TYPE INT
        DECLARE @QUERY NVARCHAR(1000)
        SET @QUERY = 'select @RESULT=count(*) from ' + ISNULL(@TableName,'') +'     WHERE '+ ISNULL(@ColumnName,'')+'='''+ ISNULL(@ValueToSearch,'') +''''
        EXEC sp_executesql @QUERY,
            N'@result int OUTPUT, @type int OUTPUT',
            @RESULT OUTPUT, 
            @TYPE OUTPUT
        IF(ISNULL(@RESULT,0)>0)
        BEGIN
            SET NOCOUNT ON;
        SELECT ' COLUMN '+ @ColumnName + ' OF TABLE ' +@TableName+ ' HAS     THIS VALUE.'
        END
        FETCH NEXT FROM @getResult INTO @TableName,@ColumnName
    END
CLOSE @getResult
DEALLOCATE @getResult

谢谢

马诺吉