InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 14:59:54 GMT20http://www.sqlservercentral.com/Forums/Topic952861-1701-1.aspxwhat value i have to give to parameter name @sp sysname in the procedure to execute as shown given below create procedure dba_GetDependencies1 @sp sysnameFri, 16 Jul 2010 11:33:38 GMTricky70ranahttp://www.sqlservercentral.com/Forums/Topic952861-1701-1.aspxHmm - this does not appear to list the same stored procedures I see with view dependencies?And I guess you should truncate Tree between runs?DougThu, 15 Jul 2010 09:22:41 GMTDouglas Osborne-456728http://www.sqlservercentral.com/Forums/Topic952861-1701-1.aspxSounds good. No, this was written on 05', I'm sure it's lot better in 08'.Thu, 15 Jul 2010 08:33:56 GMTdeepforesthttp://www.sqlservercentral.com/Forums/Topic952861-1701-1.aspxIt's just a warning, can you see the function in the results table?Thu, 15 Jul 2010 08:32:03 GMTdeepforesthttp://www.sqlservercentral.com/Forums/Topic952861-1701-1.aspxIf you are running SQL 2008, you can use sys.sql_expression_dependencies to determine the dependencies.We use the following code to build the objects in the correct order. Of course, this can only be run on a database that already has all the objects created, which is usually our Dev instance. This code has been modified a bit because we really use it to generate an ordered code script but this gives you the raw output.[code="sql"]DECLARE @Cmd varchar(max) = '';WITH SysObjs as ( SELECT name, OBJECT_ID, type FROM sys.objects WHERE type IN ('U','V','P','FN','TF','IF', 'TR') ) ,depends as ( SELECT so.name as Name, sd.referencing_id as ID, so.type as Type, sdo.object_id as DependsID, sd.referenced_entity_name as DependsName, sdo.type as DependsType, sd.referenced_server_name as DependsServerName, sd.referenced_database_name as DependsDatabaseName, sd.referenced_schema_name as DependsSchemaName, sd.is_caller_dependent as DependsIsCallerDependent, sd.is_ambiguous as DependsIsAmbiguous FROM sys.sql_expression_dependencies as sd LEFT JOIN SysObjs as so ON so.object_id = sd.referencing_id LEFT JOIN SysObjs as sdo ON sdo.object_id = sd.referenced_id OR (sd.referenced_id IS NULL AND sdo.name = sd.referenced_entity_name) WHERE ISNULL(sd.referencing_id, 0) &lt;&gt; ISNULL(sd.referenced_id,0) ) , Mapping as ( SELECT d1.Name, d1.ID, d1.Type, d1.DependsName, d1.DependsID, d2.DependsType FROM depends as d1 LEFT JOIN depends d2 ON d1.DependsID = d2.ID GROUP BY d1.Name, d1.ID, d1.Type, d1.DependsName, d1.DependsID, d2.DependsType ) , MappingWithBase as ( SELECT name, ID, Type, DependsName, DependsID, DependsType FROM Mapping UNION ALL SELECT name, object_id, type, NULL, NULL, NULL FROM SysObjs WHERE object_id NOT IN (SELECT id FROM Mapping) ) , DepMap as ( SELECT 1 as Level, name, ID, TYPE, DependsID FROM MappingWithBase WHERE ID NOT IN (SELECT DependsID FROM MappingWithBase WHERE DependsID IS NOT NULL) GROUP BY Name, ID, Type, DependsID UNION ALL SELECT Level + 1, mp.Name, mp.ID, mp.Type, mp.DependsID FROM DepMap as dm JOIN MappingWithBase as mp ON dm.DependsID = mp.ID ) SELECT * FROM ( SELECT DB_NAME() as DBName, MAX(COALESCE(so.Name, dm.name)) as Name, so.object_id, MAX(so.Type) as Type, MAX(CASE WHEN dm.Level IS NULL THEN -1 ELSE dm.Level END) as Level, DENSE_RANK() OVER (ORDER BY MAX(CASE WHEN dm.Level IS NULL THEN -1 ELSE dm.Level END) DESC) as LevelRank, ROW_NUMBER() OVER (ORDER BY MAX(CASE WHEN dm.Level IS NULL THEN -1 ELSE dm.Level END) DESC, MAX(so.Name)) as OrderNo FROM DepMap as dm FULL OUTER JOIN SysObjs as so ON dm.ID = so.object_id GROUP BY so.object_ID ) as DM ORDER BY LevelRank, OrderNo[/code]Thu, 15 Jul 2010 08:22:32 GMTGatekeeperhttp://www.sqlservercentral.com/Forums/Topic952861-1701-1.aspxi m getting the foll. error:[code="sql"]Warning: Null value is eliminated by an aggregate or other SET operation.[/code]my stored proc is calling a scalar valued function.Thu, 15 Jul 2010 01:17:26 GMTziangijhttp://www.sqlservercentral.com/Forums/Topic952861-1701-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Stored+Procedure/70504/">SP and function dependencies</A>[/B]Thu, 15 Jul 2010 00:01:16 GMTdeepforest