-1

I have a simply query but want it to run across all databases without having to union all. I would like a column that also shows the Database Name as well, so i know which company the query results came from

Here is the query:

select vendor_id, U_EXPDATE 
from vendor
where u_expdate >= convert(varchar(10), getdate() - 60, 121)
order by vendor_id
jarlh
  • 42,561
  • 8
  • 45
  • 63
Mary Mahoney
  • 53
  • 2
  • 10
  • 1
    Which dbms are you using? (The term _database_ has different meanings depending on dbms.) – jarlh Jan 30 '20 at 20:05
  • i am using Sql Manager, i have multiple databases with identical table structure for the many franchisees that we store data on. – Mary Mahoney Jan 30 '20 at 21:13
  • 1
    "SQL Manager" is not the name of a database product. It sounds more like the name of a SQL client that connects _to_ a database server. What do you get when you run `select version();` or `select @@VERSION`? –  Jan 30 '20 at 21:30
  • Does this answer your question? [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) – SMor Jan 30 '20 at 22:28

1 Answers1

-1

Figured out the code to run a query across all databases and then put into a temp table so i can view it, thank you.

use master;
go

create table #Temp (Company varchar(128), Vendor varchar(128), ExpDate DATETIME);

--exec sp_MSforeachDB ' USE [?];
EXEC sp_MsForEachDb @command1 = 
' use ?; if exists(select  * from  information_schema.tables where table_name=''vendor'' )begin 


insert into #Temp
SELECT DISTINCT
 ''?'',
vendor.vendor_id,
vendor.U_expdate
FROM vendor
WHERE vendor.[type] = ''INSURANCE''
and vendor.inactive = ''N''
end
'
drop table #Temp;
select Company, Vendor, ExpDate from #Temp order by 1, 2, 3;
Mary Mahoney
  • 53
  • 2
  • 10
  • 2
    I'd be amazed if that works - you're selecting from the table after dropping it... – Neville Kuyt Jan 31 '20 at 15:37
  • You are correct. I copy and pasted incorrectly. Bit snarky on your response. A simple correction would’ve sufficed. Not sure why we can’t offer advice without the added shock – Mary Mahoney Feb 01 '20 at 16:06
  • I apologize if I was snarky - wasn't intended as such. I don't have access to SQL Server to test any correction. – Neville Kuyt Feb 03 '20 at 10:23