How to identify non-indexed foreign keys in SQL Server

Here is a script to identify foreign keys without any supporting index in SQL Server.

The script is developed on an assumption to verify ONLY the leading column of the index and the foreign key. This is to avoid any duplicate indexes that could create without a detailed analysis.


;WITH CTE AS 
(
	SELECT A.NAME,SCHEMA_NAME(A.SCHEMA_ID) SCHEMA_NAME,OBJECT_NAME(B.REFERENCED_OBJECT_ID) PARENT_TABLE_NAME,OBJECT_NAME(B.PARENT_OBJECT_ID) CHILD_TABLE_NAME,
	B.PARENT_OBJECT_ID,B.PARENT_COLUMN_ID,C.COLUMN_NAME COLUMNNAME
	,ROW_NUMBER()OVER(PARTITION BY A.NAME,A.SCHEMA_ID,B.REFERENCED_OBJECT_ID,B.PARENT_OBJECT_ID ORDER BY C.ORDINAL_POSITION ASC) RN
	FROM SYS.FOREIGN_KEYS A
	INNER JOIN SYS.FOREIGN_KEY_COLUMNS B ON A.OBJECT_ID = B.CONSTRAINT_OBJECT_ID
	INNER JOIN INFORMATION_SCHEMA.COLUMNS C ON C.COLUMN_NAME=COL_NAME(B.PARENT_OBJECT_ID,B.PARENT_COLUMN_ID) AND OBJECT_NAME(B.PARENT_OBJECT_ID)=C.TABLE_NAME 
)
SELECT A.NAME FOREIGNKEY_NAME,A.SCHEMA_NAME,A.PARENT_TABLE_NAME,A.CHILD_TABLE_NAME,A.COLUMNNAME FORIEGNKEY_LEADCOLUMN
FROM CTE A
LEFT JOIN SYS.INDEX_COLUMNS D ON D.OBJECT_ID = A.PARENT_OBJECT_ID AND D.COLUMN_ID = A.PARENT_COLUMN_ID AND KEY_ORDINAL=1 
LEFT JOIN SYS.INDEXES D1 ON D.OBJECT_ID = D1.OBJECT_ID AND D.INDEX_ID=D1.INDEX_ID
LEFT JOIN SYS.COLUMNS E ON E.COLUMN_ID=D.COLUMN_ID AND E.OBJECT_ID=D.OBJECT_ID  
WHERE D.COLUMN_ID IS NULL AND A.RN=1
ORDER BY CHILD_TABLE_NAME ASC
Advertisements

Hi Everyone,

Here is an opportunity to work with a world class organization – ADP.
Interested people can mail their resumes to latheesh.govind@adp.com

New enhancement in SSMS in SQL Server

SSMS in SQL 2016 has come up with lots of new features. One of the features is enhanced scrollbar in SSMS. I personally believe the new enhancement in scrollbars will definitely help developers to work with SSMS with ease.

Let us look at the below picture to have an insight:

They are different color bocks on the right side of SSMS query window that shows the changes:
a. Green blocks show the saved lines of Query part in a window to disk
b. Yellow blocks show the query lines of unsaved/changed lines since last save to disk
c. Red blocks show any syntax errors in a query window
d. Blue block shows the current cursor location

It is definitely good to know to developers who plays around with SSMS very frequently.

How to identify the referenced objects of an object in SQL Server

Here is a very small script that helps you to identify the referenced objects of an object in SQL Server.


Declare @ParentObject_name sysname = 'YOUR OBJECT'
;With cte as (
Select OBJECT_NAME(referencing_id) Parent_ObjectName,referenced_entity_name Referenced_ObjectName,
		ISNULL((Select type From sys.objects where name = SED.referenced_entity_name),'**')  ObjType
From sys.sql_expression_dependencies SED where referencing_id = object_id(@ParentObject_name)
union All
Select OBJECT_NAME(referencing_id),SED.referenced_entity_name,
		ISNULL((Select type From sys.objects where name = SED.referenced_entity_name),'**')  ObjType
From sys.sql_expression_dependencies SED 
Inner join cte c On SED.referencing_id = object_id(c.Referenced_ObjectName) 
Where OBJECT_NAME(referencing_id)  SED.referenced_entity_name and c.ObjType Is NOT NULL
)
Select * From cte  

Cycle Clipboard ring in SQL Server Management Studio

“Cycle Clipboard” or “CTRL + SHIFT + V” is a short cut operation for pasting the items in the clipboard. This feature can paste last 20 items in the clipboard memory.

This is a very efficient method for developers to paste items not only the last copied, also any of the last 20 items. We need to just iterate through the clipboard memory by pressing CTRL+SHIFT+V until we get the desired item to be pasted.

Hope you enjoyed this tip, will reach you with more soon.

HASHBYTES for a large string in SQL Server

HASHBYTES function in SQL Server is used to hash the input using the algorithm specified.

This is a very efficient method to compare the string, for an example, lets compare the view definition between two different databases etc.

I do not really want to cover the usage or basic information of HASHBYTES in this post, but, a limitation and how to overcome the same. The first input parameter of HASHBYTES is the algorithm that needs to be used and the second one is the actual character or binary string that needs to be hashed.Now, the limitation is it will not accept the string more than 8000 bytes.

Lets quickly look at the example.


CREATE TABLE dbo.Test1 (c1 nvarchar(MAX));  
Insert into dbo.Test1 Select Replicate('a',4001) 
Select hashbytes('SHA1',c1) From dbo.Test1
Drop table dbo.Test1

The above code will throw an exception “String or binary data would be truncated.” as below:

To overcome the limitation, I have come up with a solution to break down the string into multiple and apply the hashing separately and later combined.

The script is as below:


Create FUNCTION [dbo].[GenerateHASHforLargeValue]
(   
    @TextValue nvarchar(max)
)

RETURNS varbinary(20)

AS
BEGIN

    if @TextValue = null
        return hashbytes('SHA1', 'null')

    Declare @TextLength as integer
	Declare @BinaryValue as varbinary(20)

    Set @TextLength = len(@TextValue)
	Declare @LenCount int  = 3500
    if @TextLength > @LenCount
    Begin
    ;With cte 
    as
    (
	Select substring(@TextValue,1, @LenCount) textval, @LenCount+1 as start, @LenCount Level,
               hashbytes('SHA1', substring(@TextValue,1, @LenCount)) hashval
	Union All 
	Select substring(@TextValue,start,Level), start+Level ,@LenCount  Level, 
               hashbytes('SHA1', substring(@TextValue,start,Level) + convert( varchar(20), hashval )) 
	From cte where Len(substring(@TextValue,start,Level))>0
    ) Select @BinaryValue = (Select Top 1 hashval From cte Order by start desc)
			return @BinaryValue
    End
    else
    Begin
	Set @BinaryValue = hashbytes('SHA1', @TextValue)
	return @BinaryValue
    End
    return null
END

If we apply the above function, then it will generate the hash code without the string truncation issue.

Hope this will help you whenever you may need to generate hash for larger strings!!!