Code formatting in SQL-Server

I have noticed that code formatting is a difficult task for many of us. I found one small plugin notepad++ in order to do the code formatting. Below are the steps.

1. Download notepad++ Download Link
2. Go Plugins menu and select Plugin manager -> Show plugin manager.
3. In the window search Poor Man's T-Sql Formatter and install.

After performing the above steps close and open your notepad++.

Now copy your code and paste it in notepad++ and run the plugin by selecting from Plugins -> Poor Man's T-Sql Formatter.

Find the foreign key and Generate the drop statement

This post will help to find to the all the constraint and generate the drop statement for all foreign key constraint.

Below is the code.

DECLARE @temp TABLE (

       RowId INT PRIMARY KEY IDENTITY(1, 1)

       ,FKConstraint NVARCHAR(200)

       ,FKConstraintTblSch NVARCHAR(200)

       ,FKConstraintTbNm NVARCHAR(200)

       ,FKConstraintClNm NVARCHAR(200)

       ,PKConstraint NVARCHAR(200)

       ,PKConstraintTblSch NVARCHAR(200)

       ,PKConstraintTblNm NVARCHAR(200)

       ,PKConstraintClmNm NVARCHAR(200)

       )

INSERT INTO @temp (

       FKConstraint

       ,FKConstraintTblSch

       ,FKConstraintTbNm

       ,FKConstraintClNm

       )

SELECT U.CONSTRAINT_NAME

       ,U.TABLE_SCHEMA

       ,U.TABLE_NAME

       ,U.COLUMN_NAME

FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE U

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME

WHERE C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @temp

SET PKConstraint = UNIQUE_CONSTRAINT_NAME

FROM @temp T

INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R ON T.FKConstraint = R.CONSTRAINT_NAME

UPDATE @temp

SET PKConstraintTblSch = TABLE_SCHEMA

       ,PKConstraintTblNm = TABLE_NAME

FROM @temp T

INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C ON T.PKConstraint = C.CONSTRAINT_NAME

UPDATE @temp

SET PKConstraintClmNm = COLUMN_NAME

FROM @temp T

INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U ON T.PKConstraint = U.CONSTRAINT_NAME

--SELECT * FROM @temp

--DROP CONSTRAINT:

SELECT 'ALTER TABLE [' + FKConstraintTblSch + '].[' + FKConstraintTbNm + ']

DROP CONSTRAINT ' + FKConstraint + ' GO'

FROM @temp

Thx,
RS