T-SQL: Script to Find the Names of Stored Procedures that Use Dynamic SQL ( Method 2 )

First method was using dynamic management function – sys.dm_sql_referenced_entities . Fun with system functions continues … Here is the second method : Using dynamic management function – sys.dm_exec_describe_first_result_set SELECT  SP_Name,error_message,error_type_desc FROM (SELECT schema_name(schema_id)+’.’+name SP_Name FROM sys.procedures ) tmpCROSS APPLY sys.dm_exec_describe_first_result_set (SP_Name, NULL, 0)WHERE error_type = 4 See Also : http://www.codefloor.com/?p=79 T-SQL Script to find Stored…

Details

TSQL Script to Find the Names of Stored Procedures that Use XQUERY

Below scripts can be used to find the names of Stored Procedures that use XQUERY : –Method 1: SELECT  SP_Name StoredProcedure FROM (SELECT schema_name(schema_id)+’.’+name SP_Name FROM sys.procedures ) tmpCROSS APPLY sys.dm_sql_referenced_entities (SP_Name, ‘OBJECT’)WHERE referenced_entity_name IN ( ‘value’,’query’)AND is_ambiguous = 1 –Method 2: SELECT schema_name(schema_id)+’.’+object_name(referencing_id) StoredProcedureFROM sys.sql_expression_dependencies  SEDJOIN sys.procedures P ON P.object_id = SED.referencing_idWHERE referenced_entity_name IN…

Details

SQL Server – Policy based management – System Views

Below query will provide the Created policies , Conditions imposed and Policy evaluation history details :USE msdb GO SELECT P.name AS PolicyName, C.name AS ConditionName, C.facet AS Facet , tmp.Name [Option], tmp.Operator, C.Obj_name ExpressionToBeValidated, tmp.ResultValue, tmp.Result , CASE WHEN P.execution_mode = 0 THENĀ  ‘On demand’ WHEN P.execution_mode = 1 THENĀ  ‘On change: prevent’ WHEN P.execution_mode…

Details

Sql Server – Object dependencies

Dependencies by creating Primary & Foreign keys:   CREATE DATABASE TEST_DEPENDENCY_DB1GO USE TEST_DEPENDENCY_DB1GOCREATE TABLE TEST_DEPENDENCY_PRIMARY(EMPID INT PRIMARY KEY,NAME VARCHAR(10))INSERT INTO TEST_DEPENDENCY_PRIMARY SELECT 1 ,’Sathya’INSERT INTO TEST_DEPENDENCY_PRIMARY SELECT 2 ,’Deepak’CREATE TABLE TEST_DEPENDENCY_SECONDARY(EMPID INT FOREIGN KEY REFERENCES TEST_DEPENDENCY_PRIMARY (EMPID),[ROLE] VARCHAR(10))INSERT INTO TEST_DEPENDENCY_SECONDARY SELECT 1 ,’Developer’INSERT INTO TEST_DEPENDENCY_SECONDARY SELECT 2 ,’DBA’ under Object Explorer,right click Table – >…

Details