Transact SQL (mssql) Return name of DB from SELECT "search"

Question

Running Microsoft SQL Server 11.0.3128
on Windows Server 2012 R2 Essentials

I am attempting to return the name of a specific database based on a supplied variable (batch file that calls SQL script).

The process, in my head, should look something like this:

 For each database in instance
       Look in the current database
              Return databasename if variable is found in column

The code I've been working with so far looks like this:

EXEC dbo.sp_MSForeachdb  '  
USE [?];        
SELECT  DB_NAME() AS DBName
        UNION SELECT
            ColumnName
            FROM dbo.Items
            WHERE ColumnName =''variable''
'

Problem is, this returns a lot more than I want it to since it returns "null" values for the databases that do not contain "variable" and creates messages for databases not containing "ColumnName".

But I can't seem to figure out how to get the specific info I want without the other stuff. First time poster, please let me know if I can improve the question.

Thanks!

So you just want the DB Name? Add `IF OBJECT_ID(''dbo.Items'') IS NOT NULL` as the line after your `USE` and lose the 2nd `SELECT` — Alex K., Jul 21 '16 at 15:25
Oh, [`sp_msforeachdb` is SUCH a terrible idea](https://www.mssqltips.com/sqlservertip/2201/making-a-more-reliable-and-flexible-spmsforeachdb/). — Aaron Bertrand, Jul 21 '16 at 15:27

score 0 · Accepted Answer · edited May 23 '17 at 10:28

EDIT: Oops, didn't realize at first you were working with mssql and not mysql. The principle below will still work; you'll just need to adjust the syntax a bit and use a user-function to replace group_concat since mssql doesn't have that.

Here's an approach without sp_MSForeachdb. Note that you will want to sanitize the parameters first.

delimiter $$

create procedure FindDatabases
(
    in varName varchar(2000),
    in tableName varchar(2000),
    in columnName varchar(2000)
)
begin
    declare selectQuery varchar(2000);

    select group_concat(
                concat('select ''', 
                    table_schema, 
                    ''' as DatabaseName from ', 
                    table_schema, 
                    '.',
                    tableName,
                    ' where ',
                    columnName,
                    ' = ''',
                    varName,
                    '''')
                separator ' union ') as DatabaseNames
    from information_schema.tables 
    where table_name = tableName
    into @selectQuery;  

    prepare preparedSql from @selectQuery;
    execute preparedSql;
    deallocate prepare preparedSql;

end $$
delimiter ;

Example usage:

call FindDatabases ( 'variable', 'Items', 'ColumnName' )

This procedure generates a sql query for each database with a table name matching the table name supplied, unions them together, and then executes them. Each query in the union returns its database name if the specified table in that database has a column matching the specified name that contains a value that matches the specified variable name. Only databases matching these requirements will be present in the query results, so you don't have to worry about null values in the results.

ADDITIONAL EDIT: As promised, here is a sqlserver version.

create procedure FindDatabases
(
    @varName varchar(2000),
    @tableName varchar(2000),
    @columnName varchar(2000)
)
as
begin
    declare @selectQuery nvarchar(2000)


    -- first, get a list of database names that contain the specified table 
    IF OBJECT_ID('tempdb.dbo.#db_temp') IS NOT NULL
        DROP TABLE #db_temp 

    CREATE TABLE #db_temp (DatabaseName SYSNAME)

    SELECT @selectQuery = (
        SELECT '
            USE [' + d.name + ']; 

            INSERT INTO #db_temp (DatabaseName)
            SELECT DB_NAME() as DatabaseName
            WHERE EXISTS(
                SELECT 1
                FROM sys.objects
                WHERE [object_id] = OBJECT_ID(''' + @tableName + ''')
                    AND [type] = ''U''
            )'
        FROM sys.databases d
        WHERE d.name NOT IN ('master', 'tempdb', 'model', 'msdb')
            AND d.state_desc != 'OFFLINE'
        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

    EXEC sys.sp_executesql @selectQuery

    -- use something like mysql's group_concat function to turn that list into a bunch of  union all select statements
    select
        @selectQuery =
        (
            SELECT LEFT(database_names , LEN(database_names ) - 10) AS     database_names
            FROM #db_temp AS extern
        CROSS APPLY
            (
                SELECT 'select ''' + DatabaseName + ''' as     DatabaseName from ' + DatabaseName + '.dbo.' + @tableName +
                ' where ' + @columnName + ' = ''' + @varName + '''' +     ' union all '
                FROM #db_temp AS intern
                FOR XML PATH('')
            ) pre_trimmed (database_names)
            GROUP BY database_names
        )       

    drop table #db_temp

    -- run those select statements
    exec sp_executesql @selectQuery
end

To run it:

exec FindDatabases 'someVar', 'Items', 'ColumnName'

I shamelessly pulled some snippets from here and here to work around the lack of a group_concat function and sqlserver's information_schema having only the local database's info and not sharing information across databases.

You wouldn't happen to have something that would work for mssql? I've been trying to get it to work in SSMS but the syntax eludes me. — Martin Halvorsen, Jul 26 '16 at 11:01
I don't have time now but I will try to make time tonight to put a sql server version together for you. I'll update my answer with that when I do. — S.C., Jul 27 '16 at 20:23

Transact SQL (mssql) Return name of DB from SELECT "search"

1 Answers1