Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sql_expression_dependencies$

  No additional text.


Syntax
CREATE VIEW sys.sql_expression_dependencies$ AS
	SELECT
		dp.depid AS referencing_id,
		0 as referencing_minor_id,
		dp.depclass AS referencing_class,
		i.name AS referencing_class_desc,
		s.name as referencing_schema_name,
		o2.name as referencing_entity_name,
		sysconv(bit, 0) AS is_schema_bound_reference,
		dp.indepclass AS referenced_class,
		i2.name AS referenced_class_desc,
		dp.indepserver AS referenced_server_name,
		dp.indepdb AS referenced_database_name,
		dp.indepschema AS referenced_schema_name,
		dp.indepname AS referenced_entity_name,
		Entity_Id(dp.indepserver, dp.indepdb, dp.indepschema, dp.indepname, dp.indepclass, sysconv(bit, dp.status & 1), dp.depid) AS referenced_id,
		0 as referenced_minor_id,
		NULL as referenced_minor_name,
		sysconv(bit, dp.status & 1) AS is_caller_dependent,
		sysconv(bit, dp.status & 4) AS is_ambiguous
	FROM sys.syssoftobjrefs dp
		LEFT JOIN sys.syspalvalues i ON i.class = 'UNCL' AND i.value = dp.depclass
		LEFT JOIN sys.syspalvalues i2 ON i2.class = 'UNCL' AND i2.value = dp.indepclass
		LEFT JOIN sys.sysschobjs o2 ON o2.id = dp.depid AND ((o2.nsclass = 0 AND o2.pclass = 1) OR o2.type IN ('TA','TR'))
		LEFT JOIN sys.sysclsobjs s ON s.id = o2.nsid AND s.class = 50	-- SOC_SCHEMA
	UNION ALL 		-- Can be a union all because we only store non-schema-bound dependencies in syssoftobjrefs and we select only schema bound dependencies
					-- from sysmultiobjrefs.
	SELECT
		dp.depid AS referencing_id,
		dp.depsubid as referencing_minor_id,
		sysconv(tinyint, CASE WHEN dp.class = 5 AND (dp.status & 8)=0 THEN 7 WHEN dp.class = 5 THEN 9 ELSE 1 END) AS referencing_class,
		i2.name AS referencing_class_desc,
		s.name as referencing_schema_name,
		o2.name as referencing_entity_name,
		sysconv(bit, 1) AS is_schema_bound_reference,
		sysconv(tinyint, CASE dp.class WHEN 1 THEN 1 WHEN 2 THEN 6 WHEN 3 THEN 10 WHEN 4 THEN 21 WHEN 5 THEN 1 END) AS referenced_class,
		i.name AS referenced_class_desc,
		NULL AS referenced_server_name,
		NULL AS referenced_database_name,
		CASE dp.class WHEN 1 THEN SCHEMA_NAME(o.nsid) WHEN 2 THEN SCHEMA_NAME(t.schid) WHEN 3 THEN SCHEMA_NAME(x.nsid) WHEN 4 THEN NULL WHEN 5 THEN SCHEMA_NAME(o.nsid) END AS referenced_schema_name,
		CASE dp.class WHEN 1 THEN o.name WHEN 2 THEN t.name WHEN 3 THEN x.name WHEN 4 THEN pf.name WHEN 5 THEN o.name END AS referenced_entity_name,
		dp.indepid AS referenced_id,
		dp.indepsubid as referenced_minor_id,
		c.name as referenced_minor_name,
		sysconv(bit, 0) AS is_caller_dependent,
		sysconv(bit, 0) AS is_ambiguous
	FROM sys.sysmultiobjrefs dp -- LEFT join with base sys tables instead of metadata views to bypass has_access check, according to security spec
		LEFT JOIN sys.sysschobjs o ON o.id = dp.indepid AND o.nsclass = 0 AND o.pclass = 1
		LEFT JOIN sys.sysscalartypes t ON t.id = dp.indepid
		LEFT JOIN sys.sysnsobjs x ON x.id = dp.indepid AND x.class = 27	-- SOC_XSDCOLLECTION
		LEFT JOIN sys.sysclsobjs pf ON pf.id = dp.indepid AND pf.class = 30
		LEFT JOIN sys.syspalvalues i ON i.class = 'UNCL' AND i.value = CASE dp.class WHEN 1 THEN 1 WHEN 2 THEN 6 WHEN 3 THEN 10 WHEN 4 THEN 21 WHEN 5 THEN 1 END
		LEFT JOIN sys.syspalvalues i2 ON i2.class = 'UNCL' AND i2.value = CASE WHEN dp.class = 5 AND (dp.status & 8)=0 THEN 7 WHEN dp.class = 5 THEN 9 ELSE 1 END
		LEFT JOIN sys.sysschobjs o2 ON o2.id = dp.depid AND ((o2.nsclass = 0 AND o2.pclass = 1) OR o2.type IN ('TA','TR'))
		LEFT JOIN sys.sysclsobjs s ON s.id = o2.nsid AND s.class = 50	-- SOC_SCHEMA
		LEFT JOIN sys.syscolpars c ON c.number = 0 AND c.id = dp.indepid AND c.colid = dp.indepsubid
	WHERE dp.class >= 1 AND dp.class <= 5 -- MRC_EXPRTOOBJ,MRC_EXPRTOTYPE,MRC_EXPRTOXMLNS,MRC_EXPRTOPRTFUNC, MRC_EXPRFILTERTOOBJ -- Gaurentees we are only selecting schema bound dependencies

 
Last revision 2008RTM
See also

  sql_expression_dependencies (View)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash