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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy