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

SQL Server – Calender details

DECLARE @StartDate DATE = ‘2014-01-01′, @EndDate DATE = ‘2014-12-31′ ;With CTEAS(SELECT @StartDate dt UNION ALL SELECT DATEADD(DAY, 1, dt) FROM CteWHERE dt < @EndDate)SELECT FORMAT ( dt, ‘D’, ‘en-US’ ) Calender,DATENAME(dayofyear, dt) DayNumber_Year,DATEPART(day, dt) DayNumber_Month,DATEPART(weekday, dt) DayNumber_Week,DATENAME(weekday, dt) DayName,IIF ( DATEPART(weekday, dt) IN (1,7) , ‘Weekend’, ‘Weekday’ ) Day,DATENAME(month, dt) MonthName,DATEPART(month, dt) MonthNumber,DATENAME(year, dt) Year,EOMONTH…

Details

SQL Server – Dynamic SQL – SQL Injection – EXEC [ QUOTENAME() , REPLACE() , EXECUTE AS ] – Sp_executesql – [ RECOMPILE ]

Recently I had mess up with dynamic SQL , So whats next !!! , I started to explore on this topic and post it here , when I googled to gather some information , but what I found was , this topic has been already drilled to the core , carved and with the sculpture…

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