-- This script permits you to search for a string in all tables in all user
-- databases on an instance. Replace the assignment of @value on Line 13 with the
-- value you want to search for.
-- If you have columns encrypted with Always Encrypted, you want to uncomment line 34
-- below. You want this line commented if you are on SQL 2014 or earlier, as this
-- column was added to sys.columns in SQL 2016.
-- © Erland Sommarskog 2020.

DECLARE @db            sysname,
        @sp_executesql nvarchar(500),
        @query         nvarchar(MAX),
        @dbstmts       nvarchar(MAX),
        @value         nvarchar(50) = 'Berglunds snabbköp'


SELECT @query =
    'DECLARE @sq char(1) = char(39)
     SELECT @dbstmts =
           (SELECT  ''IF EXISTS (SELECT *
                                 FROM '' + quotename(s.name) + ''.'' + quotename(t.name) + ''
                                 WHERE '' + quotename(c.name) + '' = @value)
                    SELECT db_name() AS DatabaseName, '' + 
                           quotename(s.name, @sq) + '' AS SchemaName, '' +
                           quotename(t.name, @sq) + '' AS TableName, '' + 
                           quotename(c.name, @sq) + '' AS ColumnName, * 
                    FROM '' + quotename(s.name) + ''.'' + quotename(t.name) + ''
                    WHERE '' + quotename(c.name) + '' = @value'' +
                    char(13) + char(10)
            FROM    sys.tables t
            JOIN    sys.schemas s ON s.schema_id = t.schema_id
            JOIN    sys.columns c ON c.object_id = t.object_id
            WHERE   type_name(c.system_type_id) LIKE ''%char%''
              AND   (c.max_length = -1 OR c.max_length >= len(@value))
            --  AND   c.column_encryption_key_id IS NULL
            FOR XML PATH(''''), TYPE).value(''.'', ''nvarchar(MAX)'')'

DECLARE dbcur CURSOR STATIC LOCAL FOR
   SELECT quotename(name) FROM sys.databases
   WHERE  database_id > 4
     AND  state = 0         -- Only online databases
     AND  is_read_only = 0
   ORDER BY name

OPEN dbcur

WHILE 1 = 1
BEGIN
   FETCH dbcur INTO @db
   IF @@fetch_status <> 0
      BREAK

   SELECT @sp_executesql = @db + '.sys.sp_executesql'

   EXEC @sp_executesql @query, N'@dbstmts nvarchar(MAX) OUTPUT, @value nvarchar(50)', 
                       @dbstmts OUTPUT, @value
   PRINT @db
   IF @dbstmts IS NOT NULL
   BEGIN
      --PRINT @dbstmts
      EXEC @sp_executesql @dbstmts, N'@value nvarchar(50)', @value
   END
END

DEALLOCATE dbcur