Sure. It's a great tool - we use it all the time to track down issues, etc.

no problem. Gotta support our sponsors. Besides, I LOVE this tool. Saves a lot of headaches.

Dangerous, getting this type of thing to a manager.

Only semi-tested, but this should get you there:

SELECT DISTINCT

OBJECT_NAME(c.object_id) AS thetable

...

Sure you can using sys.columns, sys.index_columns, etc.

But I'm curious - do you have a purpose for doing this or just curiosity?

You can also use Red Gate's SQL Search tool.

Playing off your ideas and the things bouncing around in my head, I've vome up with the following which gets me what I want:

SELECToq.*

FROM OPENQUERY(DW,'

SELECTDISTINCT

''I'' as TYPCOD,...

Why in the world do you need to do this with two different queries when all you're doing is mashing them back together?

If you need to know which ones are...

I have read-only access to the DB2 data store and that's not going to change so no hope of writing a temp table there.

Cannot use a standard/vanilla linked server query,...

Thanks, mazzz. That's the kind of info I'm looking for.

Hey Gary, your post came up when I was searching on SCOM. We're looking into evaluating it as a SQL Server monitoring tool as well. I do want...

Hey Grant, I know this one's old but it's one of the first ones that comes up on a SCOM search. Did you get this implemented? If so,...

Brandie,

If I were writing this from scratch with control of the DDL, etc. I would most definitely be writing this as an SSIS package with no dynamic SQL. ...

Pitcher:

Yes, it's a linked server. Query runs on Prod to gather the data, inserts into Arch via linked server.

Yes, it's dynamic SQL for the inserts. Terribly ugly I...

Brandie, the initial set is 500 cases, but with those cases there are 29 tables and in those tables there are at times millions of entries / table. Granted,...

Brandie:

Correct, before the switch to a batch process we did not have tempdb issues.

There are possibly some reports running (originating on Arch server but querying Prod - that's changing) but...