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

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

TSQL Script to Generate Create ColumnStore Index Script

 This article is about the TSQL script which can be used to generate create ColumnStore Index script from the database .SET CONCAT_NULL_YIELDS_NULL OFF; SELECT ‘ CREATE ‘ + I.type_desc COLLATE DATABASE_DEFAULT +’ INDEX ‘ + QUOTENAME(I.name)  + ‘ ON ‘  + QUOTENAME(Schema_name(T.Schema_id))+’.’+QUOTENAME(T.name) + ‘ ( ‘ + KeyColumns + ‘ )  WITH (‘ + —…

Details

T-SQL – INSERT Statement with Columnslist inside Stored procedure

This article shares the information about what happens when INSERT Statement is used  inside Stored procedure with & without Columns list . –Create Sample tableCREATE TABLE Test (Id INT , Name VARCHAR(20))GO–Scenario 1 – INSERT statement Without Columnslist inside Stored procedure –Create Stored procedure to insert data into tableCREATE PROCEDURE SP_Insert_WithoutColumnslistASBEGININSERT Test SELECT 1,’Sathya’ENDGO–Add new column to the tableALTER…

Details