Dynamic Database Stored Procedure on SQL Server 2016

Question

I'm trying to build a stored procedure that will query multiple database depending on the databases required.

For example:

SP_Users takes a list of @DATABASES as parameters.

For each database it needs to run the same query and union the results together.

I believe a CTE could be my best bet so I have something like this at the moment.

SET @DATABASES = 'DB_1, DB_2'  -- Two databases in a string listed

-- I have a split string function that will extract each database
SET @CURRENT_DB = 'DB_1'

WITH UsersCTE (Name, Email)
AS (SELECT Name, Email 
    FROM [@CURRENT_DB].[dbo].Users),

    SELECT @DATABASE as DB, Name, Email
    FROM UsersCTE

What I don't want to do is hard code the databases in the query. The steps I image are:

1. Split the parameter @DATABASES to extract and set the @CURRENT_DB Variable
2. Iterate through the query with a Recursive CTE until all the @DATABASES have been processed
3. Union all results together and return the data.

Not sure if this is the right approach to tackling this problem.

Answer 1

I took the valid advice from others here and went with this which works great for what I need:

I decided to use a loop to build the query up. Hope this helps someone else looking to do something similar.

CREATE PROCEDURE [dbo].[SP_Users](
    @DATABASES VARCHAR(MAX) = NULL,
    @PARAM1 VARCHAR(250),
    @PARAM2 VARCHAR(250)
)

BEGIN 
    SET NOCOUNT ON;

    --Local variables
    DECLARE 
     @COUNTER INT = 0,
     @SQL NVARCHAR(MAX) = '',
     @CURRENTDB VARCHAR(50) = NULL,
     @MAX INT = 0,
     @ERRORMSG VARCHAR(MAX)

    --Check we have databases entered
    IF @DATABASES IS NULL 
    BEGIN
        RAISERROR('ERROR: No Databases Provided, 
        Please Provide a list of databases to execute procedure. See stored procedure: 
        [SP_Users]', 16, 1) 
        RETURN 
    END

    -- SET Number of iterations based on number of returned databases
    SET @MAX = (SELECT COUNT(*) FROM 
               (SELECT ROW_NUMBER() OVER (ORDER BY i.value) AS RowNumber,  i.value 
               FROM dbo.udf_SplitVariable(@DATABASES, ',') AS i)X)

    -- Build SQL Statement 
    WHILE @COUNTER < @MAX
        BEGIN

        --Set the current database
        SET @CURRENTDB = (SELECT X.Value FROM 
            (SELECT ROW_NUMBER() OVER (ORDER BY i.value) AS RowNumber,  i.value 
            FROM dbo.udf_SplitVariable(@DATABASES, ',') AS i
            ORDER BY RowNumber OFFSET @COUNTER
            ROWS FETCH NEXT 1 ROWS ONLY) X);


        SET @SQL = @SQL + N'
                          (
                          SELECT Name, Email
                          FROM [' + @CURRENTDB + '].[dbo].Users
                          WHERE 
                              (Name = @PARAM1 OR @PARAM1 IS NULL)
                              (Email = @PARAM2 OR @PARAM2 IS NULL)
                          ) '
                          + N' UNION ALL '

            END
            PRINT @CURRENTDB
            PRINT @SQL

            SET @COUNTER = @COUNTER + 1
        END

        -- remove last N' UNION ALL '
        IF LEN(@SQL) > 11
            SET @SQL = LEFT(@SQL, LEN(@SQL) - 11)

        EXEC sp_executesql @SQL, N'@CURRENTDB VARCHAR(50), 
                                   @PARAM1 VARCHAR(250), 
                                   @PARAM2 VARCHAR(250)', 
                                   @CURRENTDB, 
                                   @PARAM1 , 
                                   @PARAM2


END

---

Split Variable Function

CREATE FUNCTION [dbo].[udf_SplitVariable]
(
    @List varchar(8000),
    @SplitOn varchar(5) = ','
)

RETURNS @RtnValue TABLE
(
    Id INT IDENTITY(1,1),
    Value VARCHAR(8000)
)

AS
BEGIN

--Account for ticks
SET @List = (REPLACE(@List, '''', ''))

--Account for 'emptynull'
IF LTRIM(RTRIM(@List)) = 'emptynull'
BEGIN
    SET @List = ''
END

--Loop through all of the items in the string and add records for each item
WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN

    INSERT INTO @RtnValue (value)
    SELECT Value = LTRIM(RTRIM(SUBSTRING(@List, 1, CHARINDEX(@SplitOn, @List)-1)))  

    SET @List = SUBSTRING(@List, CHARINDEX(@SplitOn,@List) + LEN(@SplitOn), LEN(@List))

END

INSERT INTO @RtnValue (Value)
SELECT Value = LTRIM(RTRIM(@List))

RETURN

END 

Answer 2

Using @databases:

As mentioned in the comments to your question, variables cant be used to dynamically select a database. Dynamic sql is indicated. You can start by building your template sql statement:

declare @sql nvarchar(max) = 
    'union all ' + 
    'select ''@db'' as db, name, email ' + 
    'from [@db].dbo.users ';

Since you have sql server 2016, you can split using the string_split function, with your @databases variable as input. This will result in a table with 'value' as the column name, which holds the database names.

Use the replace function to replace @db in the template with value. This will result in one sql statement for each database you passed into @databases. Then, concatenate the statements back together. Unfortunately, in version 2016, there's no built in function to do that. So we have to use the famous for xml trick to join the statements, then we use .value to convert it to a string, and finally we use stuff to get rid of the leading union all statement.

Take the results of the concatenated output, and overwrite the @sql variable. It is ready to go at this point, so execute it.

I do all that is described in this code:

declare @databases nvarchar(max) = 'db_1,db_2';

set @sql = stuff(
    (

        select      replace(@sql, '@db', value)
        from        string_split(@databases, ',')
        for xml     path(''), type

    ).value('.[1]', 'nvarchar(max)')
    , 1, 9, '');

exec(@sql);

Untested, of course, but if you print instead of execute, it seems to give the proper sql statement for your needs.

Using msForEachDB:

Now, if you didn't want to have to know which databases had 'users', such as if you're in an environment where you have a different database for every client, you can use sp_msForEachDb and check the structure first to make sure it has a 'users' table with 'name' and 'email' columns. If so, execute the appropriate statement. If not, execute a dummy statement. I won't describe this one, I'll just give the code:

declare @aggregator table (
    db sysname,
    name int,
    email nvarchar(255)
);

insert @aggregator
exec sp_msforeachdb '

    declare @sql nvarchar(max) = ''select db = '''''''', name = '''''''', email = '''''''' where 1 = 2'';

    select      @sql = ''select db = ''''?'''', name, email from ['' + table_catalog + ''].dbo.users''
    from        [?].information_schema.columns
    where       table_schema = ''dbo''
    and         table_name = ''users''
    and         column_name in (''name'', ''email'')
    group by    table_catalog
    having      count(*) = 2

    exec (@sql);

';


select      *
from        @aggregator