SQL Query all Databases

Question

I need the run the following query on many databases, I have over 100+ databases, but I don't want to pull up each database and run the query one at a time.

The User table is only listed in Database#_Account.

If the query is ran it errors out because Database#_Admin does not have User Table.

(EXAMPLE Database List)

Database:
---------------------
MASTER
Model
msdb
tempdb
Database1_Account
Database1_Admin
Database2_Account
Database2_Admin
Database3_Account
Database3_Admin

Query:

EXEC sp_MsForEachDb @command1 = SELECT "?" as DatabaseName, * 
                                FROM ?.User 
                                WHERE Name = "John" AND "?" LIKE "%_Account"

Just build a dinamic query https://www.mssqltips.com/sqlservertip/1160/execute-dynamic-sql-commands-in-sql-server/ — Juan Carlos Oropeza, Aug 21 '17 at 18:42
Also you first need to check if table exists. https://stackoverflow.com/questions/167576/check-if-table-exists-in-sql-server — Juan Carlos Oropeza, Aug 21 '17 at 18:55
The problem that I am having is running the query only on select database. I have looked at the dinamic query but it does not say anything about searching select Database. — John Williams, Aug 21 '17 at 18:55
Possible duplicate of [How to run the same query on all the databases on an instance?](https://stackoverflow.com/questions/18462410/how-to-run-the-same-query-on-all-the-databases-on-an-instance) — Tab Alleman, Aug 21 '17 at 19:24
This question is different from "How to run the same query on all the databases on an instance?" due to its a search for all database's, but the possible duplicate is making another table to export data to. — John Williams, Aug 23 '17 at 17:45

score 4 · Accepted Answer · edited Aug 21 '17 at 21:05

ms_foreachDb is still an undocumented function and it subject to change anytime. I would use a cursor for something like this.

Here is a working template to get you started:

DECLARE @tsql nvarchar(max)
DECLARE @dbname varchar(500)

DECLARE MyCur CURSOR STATIC FORWARD_ONLY FOR
    SELECT [name] 
    FROM sys.databases 
    WHERE [name] NOT IN ('tempdb')

OPEN MyCur 

WHILE (1=1)   
BEGIN   
    FETCH NEXT FROM MyCur INTO @dbname

    IF @@FETCH_STATUS <> 0 
        BREAK 

    SET @tsql = 'use ' + @dbname + ' SELECT * FROM sys.tables'
    EXEC sp_executesql @tsql 
END   

CLOSE MyCur;   
DEALLOCATE MyCur;

score 0 · Answer 2 · answered Aug 21 '17 at 18:58

You need to pass the command as an nvarchar literal, not as a query.
You need to use the correct nomenclature. You've left out the schema name. It's Database.Schema.Table, not Database.Table. I'm assuming all tables use the default dbo schema.
Write the query to test if the table exists before executing. Easiest way to do that is with IF OBJECT_ID(N'TableName') IS NOT NULL.
Avoid double quotes. They're normally field identifiers like square brackets are, so they're potentially ambiguous when used for varchar literals.

Try:

EXEC sp_MsForEachDb @command1 = N'IF OBJECT_ID(N''?.dbo.User'') IS NOT NULL SELECT ''?'' as DatabaseName, * FROM ?.dbo.User WHERE Name = ''John'' AND ''?'' LIKE ''%_Account'''

Message: Command(s) completed successfully, but did not give me the result on what database or showing me John on that database was found. — John Williams, Aug 21 '17 at 19:18
@JohnWilliams Are you running a query that you know should return data for one database? Try running that query against that database. Is your User table in the dbo schema, or is it using something else? — Bacon Bits, Aug 21 '17 at 19:30

Bloggins · Answer 3 · 2018-04-03T13:28:39.913

0

Here's the query I use to do a while loop to iterate through Databases.

Just put your code where it says PUT CODE HERE.

SET NOCOUNT ON

DECLARE @Database TABLE (DbName SYSNAME)
DECLARE @DbName AS SYSNAME

SET @DbName = ''

INSERT INTO @Database (DbName)
SELECT NAME
FROM master.dbo.sysdatabases
WHERE NAME <> 'tempdb'
ORDER BY NAME ASC

WHILE @DbName IS NOT NULL
BEGIN
    SET @DbName = (
            SELECT MIN(DbName)
            FROM @Database
            WHERE DbName > @DbName
            )

    /*
    PUT CODE HERE
    EXAMPLE PRINT Database Name
    */
    PRINT @DbName
END

To create a list of users that match certain conditions you can modify this script.

edited Apr 03 '18 at 13:28

answered Apr 03 '18 at 12:22

Bloggins

61
3

1

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/19316420) – Elvin Mammadov Apr 03 '18 at 13:27
1

I've made the suggestions highlighted. Thanks for the feedback. – Bloggins Apr 03 '18 at 13:34

SQL Query all Databases

3 Answers3