Duplicates through multiple databases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Duplicates through multiple databases

Author

Topic

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-10 : 06:43:44

Hi,

Im pretty new to SQL but here is my issue.

I have the following queries that we use:


use db001 select co#=(001), fieldvalue from fundinfotable where fieldid = '1'
use db002 select co#=(002), fieldvalue from fundinfotable where fieldid = '1'

thats just two databases db001 and db002 we have a lot more.

I would like it to display duplicates across all databases.
so if fieldvalue for co# 001 is 1234 and in co#2 there is a fieldvalue of 1234 i want it to display that.

Basically right now we run all these queries for every database and we export everything to a file and then open it in excel. we then format it it so its just co# and fieldvalue and then look for duplicates.

i would like to avoid the whole excel part and just run a query that will display any duplicates of fieldvalue across all databases in "Fundinfotable"

thanks

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-10 : 08:06:00

There is no difference for the query if tables are in the same database or not.

select {columns you want to output}
from db001..fundinfotable A
inner join db002..fundinfotable B
on {condition - how you define a duplicate, something like A.id=B.id}

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-10 : 08:17:23

create table #dbs (dbname varchar(128))
insert #dbs select name from master..sysdatabases
declare @sql varchar(8000)
declare @dbname varcar(128)
-- get all databases with table
select @dbname = ''
while @dbname < (select max(dname) from #dbs
begin
select @dbname = min(dbname) from #dbs where dbname > @dbname
select @sql = 'exec ' + @dbname + '.dbo.sp_executesql N''if not exists select * from syscobjects where name = ''''fundinfotable'''' delete #dbs where dbname = ''''' + @dbname + ''''''
exec (@sql)
end
-- get all entries for field in all datbases
use db001 select dbname=convert(varchar(128),''), fieldvalue into #a from fundinfotable where 1 = 0
select @dbname = ''
while @dbname < (select max(dname) from #dbs
begin
select @dbname = min(dbname) from #dbs where dbname > @dbname
select @sql = 'exec ' + @dbname + '.dbo.sp_executesql N''insert #a select ''''' + @dbname + ''''', fieldvalue from fundinfotable where fieldid = ''''1''''''
exec (@sql)
end
select dbname, fieldvalue
from #a
where fieldvalue in
(select fieldvalue from #a group by fieldvalue having count(*) > 1)
order by fieldvalue, dbname

There's probably mismatched quotes and such but should give you an idea.
Maybe you should be looking at the design instead.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-10 : 08:34:31

nr, you're code seems far too advanced. this will scare the business if I have them using it. I can't create a table either since this would be in production and I would have a bunch of systems and DBA folks after me if i modified anything. I'm not allowed to create, delete or modify anything.

how would this work if i want say co#=(001),co#=(002),co#=(003) and fieldvalue's


select {??}
from db001..fundinfotable A
inner join db002..fundinfotable B
inner join db003..fundinfotable C
having (count(fieldvalue)> 1) ???????

idk, something like the above?!?!

nr
SQLTeam MVY

12543 Posts

Posted - 2007-11-10 : 10:22:11

A temp table is just that. You will be creating temp tables when you run queries anyway so it shouldn't be an issue.
Your dba's shouldn't be letting you run this sort of thing at all so I wouldn't worry too much about them - they probably don't realise the affect things can have on a system.

You can do a similar thing with a CTE but will have to hard code the databases and column names

with tmp
as
(
select db = 'db001', fieldvalue from db001..fundinfotable
union all
select db = 'db002', fieldvalue from db002..fundinfotable
.....
)
select fieldvalue, dbname
from tmp
where fieldvalue in
(
select fieldvalue from tmp
group by fieldvalue
having count(*) > 1
)
order by fieldvalue, dbname

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-10 : 11:16:10

thanks, i feel more comfortable hard coding them in for now. Although your first code does interest me and I would like to learn it. its just creating a temp table similar to running a simple select query and not actually creating a table in a database? Also it uses all databases that have "fundinfotable" in it so you don't have to hard code the db's (all 25+ of them).. I just feel like if i showed my supervisors that they would flip simply because of the word 'create' and the word 'delete' in there.

for now I will work with this one. but like I said i need to learn has much as possible.


with tmp
as
(
select db = 'db001', fieldvalue from db001..fundinfotable
union all
select db = 'db002', fieldvalue from db002..fundinfotable
.....
)
select fieldvalue, dbname
from tmp
where fieldvalue in
(
select fieldvalue from tmp
group by fieldvalue
having count(*) > 1
)
order by fieldvalue, dbname

QuietRiot
Yak Posting Veteran

67 Posts

Posted - 2007-11-10 : 14:45:47

if i have a list of databases say (db001, db002, db003, etc) how can i use that in the code above instead of 54 lines of select and union all statements? temporary stored procedure? how can I do this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources