July 15, 2010 at 12:01 am
Comments posted to this topic are about the item SP and function dependencies
July 15, 2010 at 1:17 am
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.
July 15, 2010 at 8:22 am
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) = ''
;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) <> 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
/* Anything is possible but is it worth it? */
July 15, 2010 at 8:32 am
It's just a warning, can you see the function in the results table?
July 15, 2010 at 8:33 am
Sounds good. No, this was written on 05', I'm sure it's lot better in 08'.
July 15, 2010 at 9:22 am
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?
Doug
May 18, 2016 at 1:11 pm
Thanks for the script.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply