How to query current row's DB name in SQL server?

Question

TableA exists in all 4 DBs, run this,

Use DB1
go
select CurrentDB=DB_NAME(), * From DB1..TableA union all
select CurrentDB=DB_NAME(), * From DB2..TableA union all
select CurrentDB=DB_NAME(), * From DB3..TableA union all
select CurrentDB=DB_NAME(), * From DB4..TableA

always got CurrentDB ='DB1'. is there a Simple way to get DB2,3,4 when the rows are pulling from non-DB1? Trying to avoid hard code.

Is there some reason you didn't just do Select 'DB1, * From Db1.... Union all select 'DB2',... etc? — Tony Hopkinson
– Tony Hopkinson, Commented Mar 4, 2015 at 22:31
@Tony. I could, just thought someone might know a less 'hard-coded' version. — Rm558
– Rm558, Commented Mar 4, 2015 at 22:35
Less hard coded?? Hard coding is not a precentage, it is either is or it isn't. You already have to put in the database to pull from. Just type in the name and be done with it. There are other ways to do this but it doesn't provide much, if any, benefit. — Sean Lange
– Sean Lange, Commented Mar 4, 2015 at 22:38
Have a look at sp_msforeachdb, or you could do a cursor, but seeing as you've already hard-coded in the from clause I fail to see the point. — Tony Hopkinson
– Tony Hopkinson, Commented Mar 4, 2015 at 22:40
I think it's a reasonable idea to minimize the number of references to the database name if it were possible to do that. Unfortunately it's not. — shawnt00
– shawnt00, Commented Mar 4, 2015 at 23:08

Pekka · Accepted Answer · 2015-03-04 22:36:40Z

1

Create the same view in each database that returns the value of DB_NAME() as a column. Then, in the union, the each row will include the database name.

answered Mar 4, 2015 at 22:36

Pekka

3,67431 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tony Hopkinson · Accepted Answer · 2015-03-04 23:41:15Z

Try this

Use Master
set NOCOUNT ON;
Declare @db_name Varchar(12)
Declare @sql Varchar(1000)

Declare db_cursor CURSOR FOR 
  SELECT [name]
  FROM sys.databases Where [name] like 'DB[123456789]'

Open db_cursor

Fetch Next FROM db_cursor INTO @db_name
if @@FETCH_STATUS = 0
begin
  Select @sql = 'select ''' + @db_name + ''', * From ' + @db_name + '..TableA'
  Fetch Next FROM db_cursor INTO @db_name
  While @@FETCH_STATUS = 0
  begin
    select @sql = @sql + ' Union all select ''' + 
           @db_name + ''', * From ' +   @db_name + '..TableA'
    Fetch NEXT FROM db_cursor INTO @db_name
  end
  exec @sql
end
Close db_cursor
Deallocate db_cursor

Not tested, but basic idea is get all the matching db names. Build the sql statement, then exec it.

Collectives™ on Stack Overflow

How to query current row's DB name in SQL server?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related