Comments posted to this topic are about the item SP and function dependencies
i m getting the foll. error:
Warning: Null value is eliminated by an aggregate or other SET operation.
my stored proc is calling a scalar valued function.
If 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.
DECLARE @Cmd varchar(max) = ''
WHERE type IN ('U','V','P','FN','TF','IF', 'TR')
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) <> ISNULL(sd.referenced_id,0)
FROM depends as d1
LEFT JOIN depends d2
ON d1.DependsID = d2.ID
WHERE object_id NOT IN (SELECT id FROM Mapping)
1 as Level,
WHERE ID NOT IN (SELECT DependsID FROM MappingWithBase WHERE DependsID IS NOT NULL)
GROUP BY Name, ID, Type, DependsID
Level + 1,
FROM DepMap as dm
JOIN MappingWithBase as mp
ON dm.DependsID = mp.ID
DB_NAME() as DBName,
MAX(COALESCE(so.Name, dm.name)) as Name,
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
/* Anything is possible but is it worth it? */
It's just a warning, can you see the function in the results table?
Sounds good. No, this was written on 05', I'm sure it's lot better in 08'.
Hmm - this does not appear to list the same stored procedures I see with view dependencies?
And I guess you should truncate Tree between runs?
Thanks for the script.
Viewing 8 posts - 1 through 7 (of 7 total)