Sql-server – List column value for all databases that have a specific table and column

I'm guessing on the first column - replace ColumnName below with the actual column name. Also it's always useful to specify which version of SQL Server you are using - I've assumed 2005.

DECLARE @DBType CHAR(1);
SET @DBType = 'A'; -- or 'B';

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

CREATE TABLE #x(DB SYSNAME);

SELECT @sql = @sql + 'IF EXISTS (SELECT 1 FROM '
  + QUOTENAME(name) + '.sys.tables WHERE name = ''DBType'')
  INSERT #x SELECT ''' + name + '''
    FROM ' + QUOTENAME(name) + '.dbo.DBType
    WHERE ColumnName = ''' + @DBType + ''';'
FROM sys.databases;

EXEC sp_executesql @sql;

SELECT DB FROM #x;

DROP TABLE #x;

From my post about this issue here (see #1):

DECLARE 
  @Search1 NVARCHAR(4000) = N'%a03dc6109c6f53ce93203f1b85c7d31d%',
  @Search2 NVARCHAR(4000) = N'%a03dc610-9c6f-53c-e932-03f1b85c7d31d%',
  @s NVARCHAR(MAX) = N'';

;WITH t AS (
    SELECT t.[object_id], [table] = t.name, [schema] = s.name
    FROM sys.tables AS t
    INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id]
    WHERE EXISTS (
        SELECT 1 FROM sys.columns
          WHERE [object_id] = t.[object_id]
          AND (system_type_id IN (35,99) -- text, ntext
          OR (system_type_id IN (167,175,231,239) -- (n)(var)(char)
            AND (max_length = -1 OR max_length >=32) -- max or >= LEN(@Search1)
             )))
)
SELECT @s = @s + N'SELECT N''' 
    + REPLACE([schema],'''','''''') + '.' 
    + REPLACE([table], '''','''''') + ''',* 
 FROM ' + QUOTENAME([schema]) + '.' + QUOTENAME([table]) + '
 WHERE ' + STUFF((SELECT '
 OR ' + QUOTENAME(name) + ' LIKE ' + CASE 
      WHEN system_type_id IN (99,231,239) 
      THEN 'N' ELSE '' END
      + '''' + @Search1 + '''' -- run again with @Search2
    FROM sys.columns
    WHERE [object_id] = t.[object_id]
    AND system_type_id IN (35,99,167,175,231,239)
    ORDER BY name
    FOR XML PATH(''), TYPE
).value(N'.[1]', N'nvarchar(max)'),1,6,'') + ';

'
FROM t;

PRINT @s;
-- EXEC sp_executesql @s;