Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SP and function dependencies Expand / Collapse
Author
Message
Posted Thursday, July 15, 2010 12:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 1:47 PM
Points: 100, Visits: 564
Comments posted to this topic are about the item SP and function dependencies
Post #952861
Posted Thursday, July 15, 2010 1:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 2,034, Visits: 369
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.
Post #952890
Posted Thursday, July 15, 2010 8:22 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 7:25 AM
Points: 392, Visits: 813
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? */
Post #953168
Posted Thursday, July 15, 2010 8:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 1:47 PM
Points: 100, Visits: 564
It's just a warning, can you see the function in the results table?
Post #953185
Posted Thursday, July 15, 2010 8:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 4, 2014 1:47 PM
Points: 100, Visits: 564
Sounds good. No, this was written on 05', I'm sure it's lot better in 08'.
Post #953188
Posted Thursday, July 15, 2010 9:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 1:32 PM
Points: 146, Visits: 327
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
Post #953236
Posted Friday, July 16, 2010 11:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, February 22, 2014 1:11 AM
Points: 19, Visits: 40
what value i have to give to parameter name @sp sysname in the procedure to execute as shown given below

create procedure dba_GetDependencies1 @sp sysname
Post #954052
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse