在SQL Server中获取所有具有值的表格

Question

我需要在数据库中存在的任何位置查找值。 考虑我需要从数据库中找到值“Fish”。
输出我需要的是

Table Name | Column Name 
-------------------------- 
Table 1 | columnName 
Table 12 | columnName 

等..

Answer 1

试试这个，

Declare @SearchStr nvarchar(100) = 'YorValue' -- Here type your text 
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) 

SET NOCOUNT ON 

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) 
SET @TableName = '' 
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') 

WHILE @TableName IS NOT NULL 
BEGIN 
    SET @ColumnName = '' 
    SET @TableName = 
(
    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE  TABLE_TYPE = 'BASE TABLE' 
     AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName 
     AND OBJECTPROPERTY(
       OBJECT_ID(
        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) 
        ), 'IsMSShipped' 
         ) = 0 
) 

WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) 
BEGIN 
    SET @ColumnName = 
    (
     SELECT MIN(QUOTENAME(COLUMN_NAME)) 
     FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE  TABLE_SCHEMA = PARSENAME(@TableName, 2) 
      AND TABLE_NAME = PARSENAME(@TableName, 1) 
      AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') 
      AND QUOTENAME(COLUMN_NAME) > @ColumnName 
    ) 

    IF @ColumnName IS NOT NULL 
    BEGIN 
     INSERT INTO #Results 
     EXEC 
     (
      'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
      FROM ' + @TableName + ' (NOLOCK) ' + 
      ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 
     ) 
    END 
END 
END 

SELECT ColumnName, ColumnValue FROM #Results 
drop table #Results 

Answer 2

这种查询可以帮助你

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 'your column name' 
ORDER BY schema_name, table_name;