Find tables with records in multible DB's

Question

Find tables with records in multible DB's

Edvard Korsbæk

SSCertifiable

Points: 5991
More actions
October 20, 2018 at 11:50 pm

#379919

Hi all!
On my production SQL server, I have + 300 databases, whereof app. 50 has the same structure, and has a table named 'logarb'
One or two of them has records, and i want to find which.
I.E., I want a 'Result' like:
DB_Name count_logarb
bb_abc 0
bb_cje 11
BB_cba 0
-------
When I look back in the history of my questions in this forum, it began with 'How to find' or 'How to update' sort of.
The last year it has been something like this - How to use the overall system to get info, to do tasks etc. like this question
I could just use this forum, but a better Idea would be to find something like 'MSSQL system for dummies' to be able to do it myself. Any good suggestions of where to start?
Best regards on a very early sunday morning in Denmark!
Edvard Korsbæk

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

Edvard KorsbÃ¦k - Saturday, October 20, 2018 11:50 PM
Hi all!
On my production SQL server, I have + 300 databases, whereof app. 50 has the same structure, and has a table named 'logarb'
One or two of them has records, and i want to find which.
I.E., I want a 'Result' like:
DB_Name count_logarb
bb_abc 0
bb_cje 11
BB_cba 0
-------
When I look back in the history of my questions in this forum, it began with 'How to find' or 'How to update' sort of.
The last year it has been something like this - How to use the overall system to get info, to do tasks etc. like this question
I could just use this forum, but a better Idea would be to find something like 'MSSQL system for dummies' to be able to do it myself. Any good suggestions of where to start?
Best regards on a very early sunday morning in Denmark!
Edvard KorsbÃ¦k

Quick suggestion, simply do cross database queries
😎

DECLARE @SEARCH NVARCHAR(MAX) = N' SELECT [COLUMNS] FROM [{{@DBNAME}}].[SCHEMA].[TABLENAME] ;'

SELECT SDB.[name] ,REPLACE(@SEARCH,N'{{@DBNAME}}',SDB.[name]) AS SRC_STR FROM sys.databases SDB;

Edvard Korsbæk SSCertifiable Points: 5991 More actions · Answer 2

Thanks, but There is something that does not work in it.
Gives a list of the databases and a string, where some of it should be replaced with values

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 3

I've used simple partitioned views for such things in the past. You can sometimes even get away with NOT creating a partitioning constraint if you don't mind not using the "partitioned" functionality.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Edvard Korsbæk SSCertifiable Points: 5991 More actions · Answer 4

This gave a lot of errors, but works:

Create Table ##temp_fbl
(
    DBName nvarchar(200),
            Cnt int
)
Exec sp_msforeachdb '
Use [?];
Insert Into ##temp_fbl (DBName,cnt)
SELECT DB_Name(), count(*) FROM LOGAFD l
            where DB_Name() like "bbj_%"
'
Select DBName, cnt Antal From ##temp_fbl GROUP BY DBName,Cnt
drop table ##temp_fbl

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 5

Edvard KorsbÃ¦k - Monday, October 22, 2018 3:25 AM
This gave a lot of errors, but works:
Create Table ##temp_fbl
(
    DBName nvarchar(200),
            Cnt int
)
Exec sp_msforeachdb '
Use [?];
Insert Into ##temp_fbl (DBName,cnt)
SELECT DB_Name(), count(*) FROM LOGAFD l
            where DB_Name() like "bbj_%"
'
Select DBName, cnt Antal From ##temp_fbl GROUP BY DBName,Cnt
drop table ##temp_fbl

Which is one of the reasons why I suggested a "partitioned view", which wouldn't produce such errors. You're also using a Global Temp Table, which could create problems with concurrency if you ever need it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Edvard Korsbæk SSCertifiable Points: 5991 More actions · Answer 6

"Which is one of the reasons why I suggested a "partitioned view","

I must admit, that i just don't know what you are speaking about. As I wrote, I would love to have a starter on it.

Best regards

Edvard Korsbæk

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 7

Edvard KorsbÃ¦k - Monday, October 22, 2018 4:37 AM
"Which is one of the reasons why I suggested a "partitioned view","
I must admit, that i just don't know what you are speaking about. As I wrote, I would love to have a starter on it.
Best regards
Edvard KorsbÃ¦k

Read the following for a primer. The part about a "partitioning column" isn't necessary for what you're trying to do, which is read ALL the tables all the time. I'm up to my eyes at work right now but I'll be back later today for how to create such a thing dynamically (if someone doesn't beat me to it).
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-2017#partitioned-views

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 8

p.s. Eirikur has the beginning of the code with his reference to sys.databases.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ScottPletcher SSC Guru Points: 100949 More actions · Answer 9

For this particular need, you just need to touch up your original script to bit to avoid unnecessary errors:

CREATE TABLE ##temp_fbl ( DBName nvarchar(200), Cnt int )

EXEC sp_msforeachdb ' If ''?'' Like ''bbj[_]%'' Begin Use [?]; If Exists(Select 1 From sys.tables Where name = ''LOGAFD'') Exec(''Insert Into ##temp_fbl (DBName,cnt) SELECT DB_Name(), count(*) FROM dbo.LOGAFD'') End /*If*/ '

Select DBName, cnt Antal From ##temp_fbl GROUP BY DBName,Cnt drop table ##temp_fbl

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.