How to get specific database name for one common table

Question

How to get specific database name for one common table

sram24_mca

SSCrazy

Points: 2633
More actions
February 24, 2020 at 4:46 am

#3728049

DB Script for getting database name which contains one table in common?
Regards,
Ram

Viewing 15 posts - 1 through 14 (of 14 total)

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

Can you clarify a little what you're looking for here please?

Do you want to compare all the tables in all the databases on a server and find a list of databases where there are any objects in common? Is that it? Or something else?

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Do a search for "ms for each db sql server" and follow your nose to write some custom code.

--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)
Intro to Tally Tables and Functions

Thom A SSC Guru Points: 99009 More actions · Answer 3

Is that a statement, or a question? It reads like a statement, but ends in a question mark.

What are you actually after here? Are you looking for table names that appear in every database? Are you looking for specific tables that don't appear in specific databases (which means that they need to be added)?

Either of these, however, infer a design flaw; normally duplicating the same objects through your databases means you have normalisation issues, and are trying to use databases to denote information that likely should be stored in a column.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

sram24_mca SSCrazy Points: 2633 More actions · Answer 4

we are looking for a list of databases where there is one objects in common, Is there DB script ?

Thanks

sram24_mca SSCrazy Points: 2633 More actions · Answer 5

sram24_mca

SSCrazy

Points: 2633

March 2, 2020 at 5:37 am

#3730035

Sorry Thom, It was a question not statment

Ant-Green SSC Guru Points: 112965 More actions · Answer 6

Can you define the commonality conditions.

DB1 has a table called "Orders" with "28 columns" in the "DBO schema"

DB2 has a tabled called "Orders" with "69 columns" in the "SALES schema"

Is the commonality the name, or also in the definition, or also in the schema?

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 7

sram24_mca wrote:

we are looking for a list of databases where there is one objects in common, Is there DB script ?
Thanks

So any object in common or a specific object in common? We'd all like to help, but we're still very unclear on what you're looking for.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

sram24_mca SSCrazy Points: 2633 More actions · Answer 8

Table Orders Schema,Definition are same. but row may differ.

sram24_mca SSCrazy Points: 2633 More actions · Answer 9

one specific object common.

We have Object A which is common in some database, will have to retrieve the current row of Object A in all databases and insert the new row on that Object A across databases.

pietlinden SSC Guru Points: 63198 More actions · Answer 10

Maybe this will help:

https://www.databasejournal.com/features/mssql/article.php/3441031/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm

Thom A SSC Guru Points: 99009 More actions · Answer 11

sram24_mca wrote:

Table Orders Schema,Definition are same. but row may differ.

You're still not giving any clarity I'm afraid. Perhaps show us what you're after, as your descriptions are very vague; they might help us understand.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 12

sram24_mca wrote:

one specific object common.
We have Object A which is common in some database, will have to retrieve the current row of Object A in all databases and insert the new row on that Object A across databases.

So, if I understand, the plan is, compare a single table's schema definition across multiple databases to identify any that are different. Is that right?

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 13

It sounds to me like you have an identically named/structured table in multiple databases and they want to keep all of them in sync insofar as content. Is that correct?

--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)
Intro to Tally Tables and Functions

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 14

Run:

EXEC sp_msforeachdb
'USE [?];
SELECT 
    TABLE_SCHEMA, 
    TABLE_NAME, 
    DB_NAME() AS "DATABASE_NAME" 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE = 'Base Table';';

against every database in SSMS, copy/paste results to Excel and sort by TABLE_NAME, DATABASE_NAME.

Joie Andrew
"Since 1982"