June 3, 2009 at 2:24 am
Hey, my testing DB doesn't hv views or pk (:hehe:), so left to chk that...
here is the reviesed code...
/* **********************************************************
To check dependency as per object, use following values as
input of the procedure.
For,
Stored Procedure : 'P'
Views :'V'
Primary Key :'PK'
Function :'FN'
********************************************************** */
ALTER PROCEDURE uspShowDependency(@objType as char(5)) AS
BEGIN
DECLARE @PROCEDURE VARCHAR(40)
DECLARE @TABLES VARCHAR(MAX)
DECLARE @Iterate INT
SELECT @Iterate = 1
SELECT DENSE_RANK() OVER (ORDER BY O.OBJECT_ID) 'GROUPID',
OBJECT_NAME(O.OBJECT_ID) 'SP',
OBJECT_NAME(SD.DEPID) 'TABLES' INTO #GROUPS
FROM SYS.OBJECTS O LEFT JOIN SYS.SYSDEPENDS SD ON (O.OBJECT_ID=SD.ID AND SD.DEPID IN (SELECT ID FROM SYS.SYSOBJECTS WHERE TYPE='U'))
WHERE O.TYPE=@ObjType
GROUP BY O.OBJECT_ID,OBJECT_NAME(O.OBJECT_ID),OBJECT_NAME(SD.DEPID)
--select * from #GROUPS
CREATE TABLE #TEMP(ObjName VARCHAR(100), Dependency NVARCHAR(MAX))
WHILE @Iterate <= (SELECT MAX(GROUPID) FROM #GROUPS)
BEGIN
SELECT @PROCEDURE = SP,
@TABLES = COALESCE(@TABLES,'','') + TableS + ',' FROM #GROUPS WHERE GROUPID = @Iterate
INSERT INTO #TEMP SELECT @PROCEDURE, @TABLES
SELECT @PROCEDURE='', @TABLES=''
SELECT @Iterate = @Iterate + 1
END
SELECT * FROM #TEMP
DROP TABLE #GROUPS
DROP TABLE #TEMP
END
--EXEC uspShowDependency 'P'
"Don't limit your challenges, challenge your limits"
June 3, 2009 at 3:59 am
That's a nice one Kruthi, but there are some SP where they are not having any dependencies, i got a SP that says null dependencies , but when i checked it uses one table,how can i get this information,
Thanks
June 3, 2009 at 4:11 am
Such SPs might not edit any table info; may used to fetch data from tables only.
Is that so? Please check it and let me know. it will help to understand it's behavior.
"Don't limit your challenges, challenge your limits"
June 3, 2009 at 9:42 am
1) You really cannot rely on sysdepends.
2) Nor can you rely on syscomments. That old mechanism has line-wrapping issues and you will miss strings that you should hit. You can use the new SQL 2005 sys.sql_modules to get accurate non-wrapped searchable text.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
June 3, 2009 at 11:46 pm
Yes, i also found some what odd behavior of sysdepends table. I did some early search on it, but didnt find any useful info.
"Don't limit your challenges, challenge your limits"
June 4, 2009 at 12:45 am
Thanks a lot for the script........
Is "sys.sql_modules" the alternative for "sysdepends" .
Tanx 😀
June 4, 2009 at 2:38 am
Is "sys.sql_modules" the alternative for "sysdepends" .
No, both, sys.sysdepends and sys.sql_modules, have different purpose in sql server.
Actually, sys.sysdepends is still there in 2K5 and 2K8 for backward compatibility only. 2k5 has new system view sys.sql_dependencies instead sys.sysdepends, and 2k8 has sys.sql_expression_dependencies.
Ref: http://msdn.microsoft.com/en-us/library/ms187997.aspx
I have checked by changing sys.sysdepends with sys.sql_dependencies in 2K5, but got the same result. Have a look both at below.
SYS.SYSDEPENDS:
SELECT DENSE_RANK() OVER (ORDER BY O.OBJECT_ID) 'GROUPID',
OBJECT_NAME(O.OBJECT_ID) 'SP',
OBJECT_NAME(SD.DEPID) 'TABLES' INTO #GROUPS
FROM SYS.OBJECTS O LEFT JOIN SYS.SYSDEPENDS SD ON (O.OBJECT_ID=SD.ID AND SD.DEPID IN (SELECT ID FROM SYS.SYSOBJECTS WHERE TYPE='U'))
WHERE O.TYPE='P'
GROUP BY O.OBJECT_ID,OBJECT_NAME(O.OBJECT_ID),OBJECT_NAME(SD.DEPID)
select * from #GROUPS WHERE TABLES IS NULL
DROP TABLE #GROUPS
SYS.SQL_DEPENDENCIES:
SELECT DENSE_RANK() OVER (ORDER BY O.OBJECT_ID) 'GROUPID',
OBJECT_NAME(O.OBJECT_ID) 'SP',
OBJECT_NAME(SD.referenced_major_id) 'TABLES' INTO #GROUPS
FROM SYS.OBJECTS O LEFT JOIN sys.sql_dependencies SD ON (O.OBJECT_ID=SD.OBJECT_ID AND SD.referenced_major_id IN (SELECT ID FROM SYS.SYSOBJECTS WHERE TYPE='U'))
WHERE O.TYPE='P'
GROUP BY O.OBJECT_ID,OBJECT_NAME(O.OBJECT_ID),OBJECT_NAME(SD.referenced_major_id)
select * from #GROUPS WHERE TABLES IS NULL
DROP TABLE #GROUPS
"Don't limit your challenges, challenge your limits"
June 4, 2009 at 8:21 am
Eswin (6/4/2009)
Thanks a lot for the script........Is "sys.sql_modules" the alternative for "sysdepends" .
sys.sql_modules.definition contains all code for sql objects. It should be used instead of syscomments if you are looking to search code.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply