SQL Formatters – Beautify your code and coding experience

Today, I am going to introduce some of tools available for SQL Server formatting.

The list is not in order of any preferences, but just the way it is. Explore and use as your choice.

1. Format SQL – https://format-sql.com/
2. Instant SQL Formatter – http://www.dpriver.com/pp/sqlformat.htm
3. SQL Prompt from redgate – http://www.red-gate.com/products/sql-development/sql-prompt/
4. SQLinForm – http://www.sqlinform.com/
5. SQL Formatter – http://www.sql-format.com/
6. SQL beautifier – https://sourceforge.net/projects/fsqlf/

You may comment if you are using any other tools of your choice.

Advertisements

How to find index usage from cached plan in SQL Server

Today, I would like to share a script to identify the usage of index from cached plan in SQL Server. This would help us to identify the places (procs/functions etc) where the index is being used. One interesting use-case is to identify the index usage that has been introduced recently during the performance optimization.


;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlansCTE
(
	DatabaseName,SchemaName,TableName,IndexName,
	QueryText,QueryPlan,CacheObjectType,ObjectType
)
AS
(
SELECT	
        RelOp1.op.value(N'@Database', N'varchar(128)') AS DatabaseName,
	RelOp1.op.value(N'@Schema', N'varchar(128)') AS SchemaName,
	RelOp1.op.value(N'@Table', N'varchar(128)') AS TableName,
	RelOp1.op.value(N'@Index', N'varchar(128)') AS IndexName,
	cp.TEXT AS QueryText,cp.query_plan AS QueryPlan,
	cp.cacheobjtype AS CacheObjectType,cp.objtype AS ObjectType
FROM (SELECT usecounts,cacheobjtype,objtype,query.text
 ,executionplan.query_plan
 FROM sys.dm_exec_cached_plans
 OUTER APPLY sys.dm_exec_sql_text(plan_handle) as query
 OUTER APPLY sys.dm_exec_query_plan(plan_handle) as executionplan
 WHERE [text] NOT LIKE '%sys%'
 AND cacheobjtype ='compiled plan' ) cp
CROSS APPLY cp.query_plan.nodes(N'//Object') RelOp1 (op)
)
SELECT	QueryPlan,QueryText,CacheObjectType,	ObjectType,
		DatabaseName,	SchemaName,TableName,	IndexName
FROM CachedPlansCTE
WHERE CacheObjectType = N'Compiled Plan'
/*
*********************************************************
SEARCH SCENARIOS
****************
/* if you need to search for a particular index*/
and (IndexName like '%Indexname%')
/* if you need to search ONLY for SELECT queries*/
and (QueryText not like '%insert%')
and (QueryText not like '%update%')
*********************************************************
*/
OPTION (MAXDOP 1)

See Also:
How to identify Missing Index from Cached Plan in SQL Server
How do you find cached plan for a procedure in SQL Server

How to identify Missing Index from Cached Plan in SQL Server

Here, we are going to find a simple and very efficient way to identify missing indexes in SQL Server. The method is used to query cached plan in SQL Server and identify the missing index information from the Cached plan.

Please have a look and share your thoughts on the same.

Script


;WITH XMLNAMESPACES
   (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
	query_plan, n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text,
	n.value('(//MissingIndexGroup/@Impact)[1]', 'FLOAT') AS impact,
	DB_ID(REPLACE(REPLACE(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)'),'[',''),']','')) AS database_id,
	OBJECT_ID(n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)')) AS OBJECT_ID,
	n.value('(//MissingIndex/@Database)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Schema)[1]', 'VARCHAR(128)') + '.' +
    n.value('(//MissingIndex/@Table)[1]', 'VARCHAR(128)') AS statement,
	(   SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
		FROM n.nodes('//ColumnGroup') AS t(cg)
		CROSS APPLY cg.nodes('Column') AS r(c)
		WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'EQUALITY' FOR  XML PATH('')) AS equality_columns,
	(  SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
		FROM n.nodes('//ColumnGroup') AS t(cg)
		CROSS APPLY cg.nodes('Column') AS r(c)
		WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INEQUALITY' FOR  XML PATH('')) AS inequality_columns,
	(  SELECT DISTINCT c.value('(@Name)[1]', 'VARCHAR(128)') + ', '
		FROM n.nodes('//ColumnGroup') AS t(cg)
		CROSS APPLY cg.nodes('Column') AS r(c)
		WHERE cg.value('(@Usage)[1]', 'VARCHAR(128)') = 'INCLUDE' FOR  XML PATH('')) AS include_columns,
		tab.text,ObjectName
FROM
(
   SELECT query_plan,text,ObjectName   FROM (
			SELECT usecounts,cacheobjtype,objtype,query.text,object_name(query.objectid) ObjectName ,executionplan.query_plan
			FROM sys.dm_exec_cached_plans
			OUTER APPLY sys.dm_exec_sql_text(plan_handle) as query
			OUTER APPLY sys.dm_exec_query_plan(plan_handle) as executionplan
			WHERE [text] NOT LIKE '%sys%'  AND cacheobjtype ='compiled plan' ) qs
   WHERE qs.query_plan.exist('//MissingIndex')=1
) AS tab (query_plan,text,ObjectName)

CROSS APPLY query_plan.nodes('//StmtSimple') AS q(n)
WHERE n.exist('QueryPlan/MissingIndexes') = 1

First and Last funda with dates in SQL Server

This post is to provide scripts to identify FIRST and LAST usages for a given date.


--How to get first day of a month 
Declare @Date Datetime = GETDATE()
SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0);

--How to get Last day of a month 
Declare @Date Datetime = GETDATE()
SELECT DATEADD(DAY,-1,DATEADD(mm, DATEDIFF(m,0,@Date)+1,0));

--How to get first day of a Calendar Year 
DECLARE @DATE DATETIME = GETDATE()
SELECT DATEADD(YY, DATEDIFF(YY,0,@DATE), 0) AS STARTOFYEAR

--How to get Last day of a Calendar Year 
DECLARE @DATE DATETIME = GETDATE()
SELECT DATEADD(YY, DATEDIFF(YY,0,@DATE) + 1, -1) AS ENDOFYEAR

I will add more usages of FIRST/LAST day to the above list as I come across in future.

How to Identify Referenced Tables(Child) recursively and its Keys for a Parent Table in SQL Server

Question:

Identify foreign key references OR Referenced Tables and its key(s) for a Parent table in SQL Server.

Script:


;WITH CTE AS
(

	SELECT OBJECT_NAME(PARENT_OBJECT_ID) CHILDTABLE, TYPE,NAME,OBJECT_NAME(REFERENCED_OBJECT_ID) PARENTTABLE,
	DELETE_REFERENTIAL_ACTION_DESC, UPDATE_REFERENTIAL_ACTION_DESC FROM SYS.FOREIGN_KEYS
	
	--SEARCH TABLENAME (PARENT TABLE)
	WHERE OBJECT_NAME(REFERENCED_OBJECT_ID) = 'ParentTable Name'

	UNION ALL

	SELECT OBJECT_NAME(PARENT_OBJECT_ID), A.TYPE,A.NAME,OBJECT_NAME(REFERENCED_OBJECT_ID),
	A.DELETE_REFERENTIAL_ACTION_DESC, A.UPDATE_REFERENTIAL_ACTION_DESC FROM SYS.FOREIGN_KEYS A
	INNER JOIN CTE B ON OBJECT_NAME(A.REFERENCED_OBJECT_ID) = B.CHILDTABLE
	WHERE PARENTTABLE !=  CHILDTABLE
	
)
,CTE1 AS
(
	SELECT DISTINCT A.*,B.COLUMN_NAME  FROM CTE A
	INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
		ON A.NAME = B.CONSTRAINT_NAME
)
SELECT 
	DISTINCT CHILDTABLE,PARENTTABLE,
	KEY_COLUMNS=  REPLACE(
					(
					SELECT A.COLUMN_NAME AS [data()]
					FROM CTE1 A
					WHERE A.NAME = B.NAME AND A.CHILDTABLE = B.CHILDTABLE AND A.PARENTTABLE = B.PARENTTABLE
					FOR XML PATH ('') )
					, ' ', ',') 
FROM CTE1 B OPTION(MAXRECURSION 32767)

sp_msforeachdb: Changing recovery model for all databases

Introduction:

Here is another script that used sp_msforeachdb to change the recovery model for all the databases.

Note: There is a caveat that changing the recovery model is not recommended without thorough knowledge, but here we are only discussing the technical aspect.


Select name,recovery_model_desc From Sys.databases

--To avoid/exception on applying the change, you may add your databases in the list.
EXEC sp_msforeachdb '
IF ''?'' not in (''tempdb'',''master'',''msdb'',''model'',''Reportserver'',''ReportserverTempDB'')
begin
    exec (''ALTER DATABASE [?] SET RECOVERY SIMPLE;'')
end
'

Select name,recovery_model_desc From Sys.databases

See Also:

You may be interested to know more on sp_MSforeachdb usage, please visit the below links

https://gallery.technet.microsoft.com/T-SQL-Script-Get-Table-Row-94e49d01
https://gallery.technet.microsoft.com/How-to-change-datacompressi-47dfce48
https://latheeshnk.wordpress.com/2015/05/06/identify-references-of-an-object-in-sql-server/

Identify references of an object in SQL Server

Problem Statement:

Today, there was a requirement from one of my colleague “How to identify a function references in SQL Server?”.

Infact, He was looking for a script to identify the reference places(objects) where the function is being used in SQL Server. Please find the script below that we used to identify the references.

Script:

DECLARE @ObjectName NVARCHAR(100)
SET @ObjectName = N'split' --Give your function/proc name

--Table variable to hold the results from different databases
Declare @ResultTable Table(SourceSchema sysname, Sourceobject sysname, ReferencedDB sysname, ReferencedSchema sysname, ReferencedObject sysname)

Declare @MyQuery NVARCHAR(MAX) = N'
USE [?]
SELECT DISTINCT
	SourceSchema      = OBJECT_SCHEMA_NAME(sed.referencing_id)
	,SourceObject     = OBJECT_NAME(sed.referencing_id)
	,ReferencedDB     = ISNULL(sre.referenced_database_name, DB_NAME())
	,ReferencedSchema = ISNULL(sre.referenced_schema_name,
	OBJECT_SCHEMA_NAME(sed.referencing_id))
	,ReferencedObject = sre.referenced_entity_name
FROM sys.sql_expression_dependencies sed
CROSS APPLY sys.dm_sql_referenced_entities(OBJECT_SCHEMA_NAME(sed.referencing_id) + ''.'' +       	                           OBJECT_NAME(sed.referencing_id), ''OBJECT'') sre
WHERE sed.referenced_entity_name like ''%' + @ObjectName + '%'' AND sre.referenced_entity_name like ''%' + @ObjectName + '%''';

Insert into @ResultTable
EXEC sp_MSforeachdb  @MyQuery

Select * From @ResultTable

The above script uses sp_MSforeachdb to execute the query in all databases available in the SQL Server.

Please note that sp_MSforeachdb is an undocumented procedure, Microsoft may change the functionality or definition of this Stored Procedure at any time.

See Also:

You may be interested to know more on sp_MSforeachdb usage, please visit the below links

https://gallery.technet.microsoft.com/T-SQL-Script-Get-Table-Row-94e49d01
https://gallery.technet.microsoft.com/How-to-change-datacompressi-47dfce48