August 19, 2010 at 1:47 am
Hey..
when i tried executing this code.. this works fine..but it will not give the list of table that are present in different schema and different databases....(or) let me know how come i get the depends object from different schemas and differnt databases..Could anyone plzz help me out in this.....
SELECT DISTINCT
o.id, o.name AS 'Procedure_Name' , oo.name AS 'Table_Name', d.depid
--, d.depnumber -- comment this out returns unique tables only
FROM sysdepends d, sysobjects o, sysobjects oo
WHERE o.id=d.id
AND o.name= 'sp_Build_Claim' -- Stored Procedure Name
AND oo.id=d.depid
--and depnumber=1
ORDER BY o.name,oo.name
Thnaks in Advance
Anil Inampudi.
August 23, 2010 at 7:02 am
Try using sys.schemas
SELECT distinct o.id, s.Name as 'Procedure_Schema', o.name AS 'Procedure_Name' ,
ss.Name as 'Table_Name', oo.name AS 'Table_Name', d.depid
FROM sysdepends d
inner join sysobjects o
on o.id=d.id
inner join sys.schemas s
on o.UID = s.schema_id
inner join sysobjects oo
on oo.id=d.depid
inner join sys.schemas ss
on oo.UID = ss.schema_id
--AND o.name= 'sp_Build_Claim'
ORDER BY o.name,oo.name
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 23, 2010 at 11:20 pm
Hey Mike,
Thanks for your assistance...this code is rearlly helpfull for fetching the depends objects with different schema as well... but in my sproc(stored procedure).i need to acesses the data from different databases(like <servername>.<schema>.<tablename>).So, how can i access the depends from different databases that are present in my sproc...
Thanks in advance
Anil Inampudi
August 24, 2010 at 6:25 am
As long as you have access to the other databases, you can run
SELECT distinct o.id, s.Name as 'Procedure_Schema', o.name AS 'Procedure_Name' ,
ss.Name as 'Table_Name', oo.name AS 'Table_Name', d.depid
FROM <Database>.sys.sysdepends d
inner join <Database>.sys.sysobjects o
on o.id=d.id
inner join <Database>.sys.schemas s
on o.UID = s.schema_id
inner join <Database>.sys.sysobjects oo
on oo.id=d.depid
inner join <Database>.sys.schemas ss
on oo.UID = ss.schema_id
ORDER BY o.name,oo.name
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 13, 2010 at 11:45 pm
very useful one .. thank u guys...
December 13, 2010 at 11:57 pm
and also u may get ur affected table(where insert,update,delete performs) will be get by adding
"and d.resultobj = 1" to your query
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply