USE master

--#region fetch list of scalar functions
IF OBJECT_ID('tempdb..#scalar')IS NOT NULL BEGIN;
    DROP TABLE #scalar;
END;

CREATE TABLE #scalar(
    id INT NOT NULL IDENTITY
    ,schemaName VARCHAR(MAX) NOT NULL 
    ,objectName VARCHAR(MAX) NOT NULL 
    ,dbSource VARCHAR(MAX) NOT NULL 
);

INSERT INTO #scalar ([schemaName],
                            [objectName],
                            [dbSource])
SELECT s.[name] AS objectName, o.[name] AS objectSchema, 'arizona' AS dbSrc
FROM arizona.sys.[objects] o
    JOIN arizona.sys.[schemas] s ON s.[schema_id] = o.[schema_id]
WHERE o.[type_desc] LIKE 'SQL_SCALAR_FUNCTION';

INSERT INTO #scalar ([schemaName],
                            [objectName],
                            [dbSource])
SELECT s.[name] AS objectName, o.[name] AS objectSchema, 'apos_Read' AS dbSrc
FROM [ActivePos_read].sys.[objects] o
    JOIN [ActivePos_read].sys.[schemas] s ON s.[schema_id] = o.[schema_id]
WHERE o.[type_desc] LIKE 'SQL_SCALAR_FUNCTION';

INSERT INTO #scalar ([schemaName],
                            [objectName],
                            [dbSource])
SELECT s.[name] AS objectName, o.[name] AS objectSchema, 'apos_write' AS dbSrc
FROM [ActivePos_write].sys.[objects] o
    JOIN [ActivePos_write].sys.[schemas] s ON s.[schema_id] = o.[schema_id]
WHERE o.[type_desc] LIKE 'SQL_SCALAR_FUNCTION';

INSERT INTO #scalar ([schemaName],
                            [objectName],
                            [dbSource])
SELECT s.[name] AS objectName, o.[name] AS objectSchema, 'apos_server' AS dbSrc
FROM [ActivePos_server].sys.[objects] o
    JOIN [ActivePos_server].sys.[schemas] s ON s.[schema_id] = o.[schema_id]
WHERE o.[type_desc] LIKE 'SQL_SCALAR_FUNCTION';


INSERT INTO #scalar ([schemaName],
                            [objectName],
                            [dbSource])
SELECT s.[name] AS objectName, o.[name] AS objectSchema, 'ActiveSystemClient' AS dbSrc
FROM [ActiveSystemClient].sys.[objects] o
    JOIN [ActiveSystemClient].sys.[schemas] s ON s.[schema_id] = o.[schema_id]
WHERE o.[type_desc] LIKE 'SQL_SCALAR_FUNCTION';

INSERT INTO #scalar ([schemaName],
                            [objectName],
                            [dbSource])
SELECT s.[name] AS objectName, o.[name] AS objectSchema, 'ActiveSystemServer' AS dbSrc
FROM [ActiveSystemServer].sys.[objects] o
    JOIN [ActiveSystemServer].sys.[schemas] s ON s.[schema_id] = o.[schema_id]
WHERE o.[type_desc] LIKE 'SQL_SCALAR_FUNCTION';

--#endregion fetch list of scalar functions

/* declare variables */
IF OBJECT_ID('tempdb..#res')IS NOT NULL BEGIN;
    DROP TABLE #res;
END;

CREATE TABLE #res(
    [db_name] VARCHAR(MAX) NOT NULL 
    ,[schema_name] VARCHAR(MAX) NOT NULL 
    ,[object_name] VARCHAR(MAX) NOT NULL 
    ,[matching_scalar_term] VARCHAR(MAX) NOT NULL 
);

DECLARE @q NVARCHAR(MAX)
DECLARE @db_name VARCHAR(MAX);
DECLARE @fx_name VARCHAR(MAX);
DECLARE @tpl_search VARCHAR(MAX)='
USE @db@

INSERT INTO #res([db_name],[schema_name],[object_name],[matching_scalar_term])
SELECT DB_NAME() AS [db_name]
       ,s.[name] AS [schema_name]
       ,o.[name] AS [object_name]
       ,''@term@'' AS [scalar_matching]
  FROM sys.objects o
  JOIN sys.sql_modules m
    ON m.[object_id] = o.[object_id]
  JOIN sys.schemas s
    ON s.[schema_id] = o.[schema_id]
WHERE m.definition LIKE ''%@term@(%''
AND s.[name]<>''@term@'';
'

DECLARE csr_db CURSOR FAST_FORWARD READ_ONLY FOR 
    SELECT name
    FROM sys.databases d
    WHERE [d].[database_id] > 4;

OPEN csr_db

FETCH NEXT FROM csr_db INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT REPLICATE('-', 30);
    PRINT '--- Switching to '+@db_name;
    PRINT REPLICATE('-', 30);
    

    DECLARE csr_term CURSOR FAST_FORWARD READ_ONLY FOR 
        SELECT [s].[objectName]
        FROM #scalar s
        WHERE s.[dbSource] = @db_name;
    OPEN [csr_term]
    FETCH NEXT FROM [csr_term] INTO @fx_name;
    WHILE @@FETCH_STATUS= 0
    BEGIN
        
        SELECT @q = REPLACE(REPLACE(@tpl_search,'@db@', @db_name), '@term@', @fx_name);

        --PRINT @q;
        EXEC [sys].[sp_executesql]
            @q
            ,N''
            ;
        FETCH NEXT FROM [csr_term] INTO @fx_name;
    END

    CLOSE [csr_term];
    DEALLOCATE [csr_term];

    FETCH NEXT FROM csr_db INTO @db_name
END

CLOSE csr_db
DEALLOCATE csr_db

SELECT * 
FROM [#res];