July 30, 2003 at 5:34 am
Hi!
I have a table named BLOCKS_HISTORY, that holds two similar fields:
who_waitresource and block_waitresource
Need to write a query like:
select who_waitresource, block_waitresource,
(select a,b,c from sysobjects
where sysobjects.id is part of who_waitresource),
(select a,b,c from sysobjects
where sysobjects.id is part of block_waitresource)
from BLOCKS_HISTORY
Thanks.
July 30, 2003 at 6:24 am
What do who_waitresource and block_waitresource contain. Please specifiy their datatypes and some sample data would be useful. What do you mean by 'is part of'.
Edited by - davidburrows on 07/30/2003 06:24:14 AM
Far away is close at hand in the images of elsewhere.
Anon.
July 30, 2003 at 6:53 am
Fields who_waitresource and block_waitresource are both nchar(256):
[who_waitresource] [nchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[block_waitresource] [nchar] (256) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
The data look like:
TAB: 11:1687077892 [[COMPILE]]
TAB: 11:1364668405 []
KEY: 12:651253475:1 (0e00bcea919b)
PAG: 7:1:39846
11:1:935464
sysobjects.id is integer:
[id] [int] NOT NULL
looks like 1364668405
So 1364668405 is part of TAB: 11:1364668405 []
Now understanding that I need a function to this query like fn_dbname
(it will need to take who_waitresource or block_waitresource as a parameter and return
the database name out of it: KEY: 12:651253475:1 (0e00bcea919b) - 12 is database id)
, since all objects are in different databases, so the part
(select a,b,c from sysobjects
where sysobjects.id is part of who_waitresource)
will look like
(select a,b,c from fn_dbname(who_waitresource).sysobjects
where sysobjects.id is part of who_waitresource)
Gets very complicated...
Is it still possible to do this?
July 30, 2003 at 8:16 am
Just found how to symplify this:
I will create a single table, named all_dbs_sysobjects with dbname column and all other columns that are present in sysobjects table, copy all database objects info into this table and think about how to do the rest 😉
So, how about a query with one sysobjects table?
July 30, 2003 at 8:48 am
This might be near
as you mentioned create table of database sysobjects
CREATE TABLE #obj (dbid int,dbname varchar(128),objid int,objname varchar(128))
insert sysobject details
EXEC sp_MSforeachdb @command1 = "INSERT INTO #obj SELECT d.dbid,d.name,s.id,s.name FROM master..sysdatabases d CROSS JOIN ?.dbo.sysobjects s WHERE d.name = '?'"
then try a match
SELECT *
FROM #obj o
INNER JOIN BLOCKS_HISTORY b
ON o.dbid = CAST(SUBSTRING(who_waitresource,CHARINDEX(':',who_waitresource)+1,CHARINDEX(':',who_waitresource,CHARINDEX(':',who_waitresource)+1)-CHARINDEX(':',who_waitresource)-1) AS int)
AND CHARINDEX(CAST(objid AS varchar),b.who_waitresource) > 0
note this will assume dbid is number between 1st and 2nd colon (:) and object id is anywhere in who_waitresouce (might get mismatches)
Hope this helps
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply