List SQL table hierarchically based on Foreign Key and Primary Key Relationship

The below query help us to list the table hierarchically based on Foreign Key and Primary key relationship:


WITH Fkeys AS (
    SELECT DISTINCT
         OnTable       = OnTable.name
        ,AgainstTable  = AgainstTable.name
    FROM
        SYSFOREIGNKEYS fk
        INNER JOIN SYSOBJECTS onTable
            ON fk.fkeyid = onTable.id
        INNER JOIN SYSOBJECTS againstTable 
            ON fk.rkeyid = againstTable.id
    WHERE 1=1
        AND AgainstTable.TYPE = 'U'
        AND OnTable.TYPE = 'U'
        -- ignore self joins; they cause an infinite recursion
        AND OnTable.Name <> AgainstTable.Name
    )
,MyData AS (
    SELECT
         OnTable = o.name
        ,AgainstTable = FKeys.againstTable
    FROM
        SYS.OBJECTS O
        LEFT JOIN FKeys
            ON  o.name = FKeys.onTable
    WHERE 1=1
        AND o.type = 'U'
        AND o.name NOT LIKE 'sys%'
    )
,MyRecursion AS (
    -- base case
    SELECT
         TableName    = OnTable
        ,Lvl    = 1
    FROM
        MyData
    WHERE 1=1
        AND AgainstTable IS NULL
    -- recursive case
    UNION ALL SELECT
         TableName    = OnTable
        ,Lvl          = r.Lvl + 1
    FROM
        MyData d
        INNER JOIN MyRecursion r
            ON d.AgainstTable = r.TableName
)
SELECT
     ROW_NUMBER() OVER (ORDER BY MAX(lvl) ASC) AS SNO
     ,Lvl = MAX(Lvl)
    ,TableName
 FROM
    MyRecursion
GROUP BY
    TableName
ORDER BY
     1 DESC
    ,2 DESC

How to Identify Foreign Keys Without Index

The below query helps you to identify the foreign key column in a Database\Tables without any index.

SELECT SCHEMA_NAME(t.schema_id) AS SchemaName

,t.name AS TableName

,fk.name AS ConstraintName

,c.name AS ColumnName

FROM sys.tables t

JOIN sys.columns c ON c.object_id = t.object_id

JOIN sys.foreign_keys fk ON fk.parent_object_id = t.object_id

JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id

AND fkc.parent_column_id = c.column_id

LEFT JOIN sys.index_columns ic ON ic.object_id = fkc.parent_object_id

AND ic.column_id = fkc.parent_column_id

WHERE ic.object_id IS NULL

ORDER BY t.name

Safe File Transfer via WinSCP in SSIS

The below steps help you to transfer a file to FTP or SFTP server using WinSCP tool in SSIS package:

Step 1: First download and instal WinSCP tool in your system.

Step 2: Create an SSIS  with Execute Process Task.

Step 3: Create a batch text file (e.g., WinScp_UploadToSFTP.txt) with below commands:

         ------------------------------------------------------------------------------------------------------
        option batch on
        option confirm off
        open sftp://User:password@server -hostkey="ssh-rsa..."
        lcd G:\\ECWorkArea\\Sales
        option transfer binary
        cd MDRUser
       Put *.xml
      close
      exit
-------------------------------------------------------------------------------------------------------------

The above code will load all XML file in th ementiond location to th eSFTP site.


Step 4: Configure Execute Process Task as shown below:

            Executables: C:\Program Files (x86)\WinSCP\WinSCP.com
            Arguments: -script="G:\ECWorkArea\Sales\WinScp_UploadToSFTP.txt"
          

SSIS Error: The Transaction Manager is not available

This type of error occurs when SSIS Transaction Option is set to "required". To resolve this error open a command prompt and restart the MSTDC service as shown below:

>NET START MSDTC

If you receive any messages like the Transaction service in not available, then execute below commands:

> MSDTC -install
>NET START MSDTC

Using GPG File Encryption in SSIS

Step 1: First install gnupg-w32cli-1.4.9.exe (or latest version) GPG tool in your system.

Step 2: Create a new ssis package.

Step 3: Add Execute Process Task.

Step 4: To encrypt a file, configure Execute Process task as shown below:

Executables: C:\Program Files (x86)\GNU\GnuPG\gpg.exe
Arguments: --yes --always-trust  --recipient Masterworks --output  Filename.GPG --encrypt Filename.txt
WorkingDirectory: D:\File Path....

Step 4A: To decrypt a file, add below code in arguments:

--yes --always-trust --passphrase xxxxxx --output Filename.txt -d Filenname.GPG

Enter your GPG password in place of xxxxxxx .

Batch Script to execute SQL command and Save the output as text file

Follow the below steps to execute SQL command and Save the output as text file through batch file:

Step 1: Prepare an SQL query and save the query as sql file (e.g., Test.sql).

Step 2: Create batch file as shown below:
--======================================================================
@ECHO OFF
SETLOCAL

REM Build an pipe delimited text file
SET PATH = C:\Documents and Settings\cst_varadhmv\Desktop\Iteration24\Train 3;
SqlCmd -S DDWSBDB01CS -d EDW20 -s"|" -E -i "C:\Documents and Settings\cst_varadhmv\Desktop\Iteration24\Train 3\Test.sql" -o "C:\Documents and Settings\cst_varadhmv\Desktop\Iteration24\Train 3\Test.Txt"
ECHO.

echo Completed Successfully at %date% %time%>>_date_.txt
--====================================================================

Step 3. Run the batch file, you can notice a text file created in the specified path with the SQL query output.

To get quotes delimited output use the following:
SqlCmd -S 192.2.200.99 -E -s'\t' -i "E:\Work Area\FOF\Iteration24\Employee.sql" -o "E:\Work Area\FOF\Iteration24\OutputQuotes.txt"

To get tab delimited output use the following:
SqlCmd -S 192.2.200.99  -i "E:\Work Area\FOF\Iteration24\Employee.sql" -o "E:\Work Area\FOF\Iteration24\OutputQuotes.txt"

Generating XML or Text file from T-SQL Query Output

--ENABLE XP_CMDSHELL===========================================================

-- To allow advanced options to be changed.
EXEC
sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC
sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
--=============================================================================

--Create Pipe-Delimited Text file
EXEC
master..xp_cmdshell
'bcp "Select * from [Bids].dbo.Employee" queryout "E:\Work Area\FOF\Iteration24\Text.txt" -t"|" -c -T -x'
--Create Xml fileEXEC
master..xp_cmdshell
'bcp "Select * from [Bids].dbo.Employee for XML auto" queryout "E:\Work Area\FOF\Iteration24\Text.xml" -c -T -x'

--DISABLE XP_CMDSHELL================================================================

-- To allow advanced options to be changed.
EXEC
sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
--To enable the feature.EXEC
sp_configure 'xp_cmdshell', 0
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

GPG File encryption Commands

First install gnupg-w32cli-1.4.9.exe (or latest version) GPG tool in your system:

TO ENCRYPT FILE
-----------------------------
C:\Program Files (x86)\GNU\GnuPG>gpg -e -r "name" filepath

Here 'name' represents the username obtained while generating GPG key.

E.g., C:\Program Files (x86)\GNU\GnuPG>gpg --yes --always-trust  --encrypt --recipient Mahadevan C:\Users\Mahadevan\Desktop\XML\FOTF.XML

TO DECRYPT FILE
-----------------------------

 C:\Program Files (x86)\GNU\GnuPG>gpg --yes --always-trust --passphrase xxxxxx --output C:\Users\Mahadevan\Desktop\XML\FOTFB.XML -d C:\Users\Mahadevan\Desktop\XML\FOTF.XML.GPG

Here 'xxxxx' represents the password obtained while generating GPG key.


TO GENERATE GPG KEY
---------------------------------------
Follow the steps in the below image:

TO VIEW THE GPG KEYS
-------------------------------------
C:\Program Files (x86)\GNU\GnuPG>gpg --List-keys


TO EDIT THE GPG KEYS
-------------------------------------
C:\Program Files (x86)\GNU\GnuPG>gpg --Edit-keys