Copying data from SQL Server to Excel using BCP utility

Below is the sample code to copy data from SQL server to Excel through BCP (Bulk Copy Program) uitlity.

declare @logtext varchar(100)
declare @cmd varchar(1000)
--set @logtext = 'Select top 10 * from Server.DB.Table'
SET @cmd = 'bcp "Select top 10 * from Server.DB.Table" queryout "c:\test\check.csv" -c -S Test/BK -T -t,'
EXEC master..XP_CMDSHELL @cmd

Set and Verify the Retention Value for Change Data Capture

To set and verify the retention period from the default 3 days to 5 days.

sp_cdc_change_job @job_type='cleanup', @retention=7200

The value 7200 represents the number of minutes in a period of 5 days. All was well, but they recently asked how they can verify that the change was made. Well, it turns out the values are stored in the msdb database, in a table called dbo.cdc_jobs. Below is the quick query to allow them to test any database with CDC enabled, to find the retention value:

SELECT [retention]

FROM [msdb].[dbo].[cdc_jobs]

WHERE [database_id] = DB_ID()

    AND [job_type] = 'cleanup'

Update values in multiple tables using single script

Below is the script used to update values in multiple tables. This is the script I used to update according to my requirement. Kindly modify it as needed.

DECLARE @oldCompanyid INT, @newcompanyid INT, @oldCompanyCode VARCHAR(50) 

        DECLARE @t Table(Name VARCHAR(100), MaxId INT)
        INSERT INTO @t(Name, MaxId)
        SELECT TableName ,maxAuditTaskId FROM dbo.AuditTaskSummary WHERE TableName = 'SourceCode' and PackageName = 'pkg_Sourcecode'
        
        SELECT @oldCompanyid = @CompanyIdOld --7
        SELECT @newcompanyid = 3
        
        CREATE TABLE #t1(RowNum INT, TableName VARCHAR(100), QueryData VARCHAR(1000))
        DECLARE @maxRow int, @minRow int
        INSERT INTO #t1 (RowNum, TableName, QueryData)
        SELECT ROW_NUMBER () OVER(ORDER BY T.name DESC) RowNum,T.name AS tableName, 
        'UPDATE ' + SCHEMA_NAME(t.schema_id) + '.' 
        + T.NAME + ' SET CompanyID  = '+CAST (@newcompanyid AS VARCHAR(10)) + ',' 
        + 'Audittaskid =' +CAST (ATS.maxAuditTaskId+1 AS VARCHAR(10)) 
        + ' WHERE CompanyID='+ CAST(@oldCompanyid AS VARCHAR(10)) +';' AS QueryData
        FROM sys.tables as T
        INNER JOIN sys.COLUMNS C ON t.OBJECT_ID = c.OBJECT_ID
        INNER JOIN (SELECT TableName ,maxAuditTaskId FROM dbo.AuditTaskSummary WHERE TableName NOT IN ('SourceCode') 
        UNION 
        SELECT Name, maxid FROM @t) ATS ON ATS.TableName = T.name
        WHERE c.name ='CompanyID' AND SCHEMA_NAME(t.schema_id) = 'dbo' 
        AND t.name NOT IN ('Company','Company_backup')
        ORDER BY SCHEMA_NAME(t.schema_id), t.name;

SELECT * FROM #t1
SELECT @maxRow = (SELECT MAX(RowNum) FROM #t1),  @minRow = (SELECT MIN(RowNum) FROM #t1)
SELECT @maxRow, @minRow

WHILE @maxRow >= @minRow
BEGIN
DECLARE @tablevalue VARCHAR(1000), @tablename VARCHAR(100)
SELECT @tablevalue = (SELECT QueryData from #t1 WHERE RowNum = @minRow), @tablename = (SELECT tablename FROM #t1 WHERE RowNum = @minRow)

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename 
AND COLUMN_NAME = 'CompanyId')
EXEC (@tablevalue);
SET @minRow = @minRow +1
END
DROP TABLE #t1;

Calling the case condition inside the partition using row_number

,ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY CASE [status]
WHEN 'active' THEN 1
WHEN 'new' THEN 2
WHEN 'cancelled' THEN 3
ELSE 4
END) AS RN

List SQL 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

T-SQL Query to get the SQL Agent job exectuted date time and duration

Select job_name, run_datetime, run_duration
From
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime

To drop all tables

SQL server is providing a nice option to drop all the tables at a stretch. Below is the query to achieve the same

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

To drop single table:

EXEC sp_MSforeachtable @command1 = "DROP TABLE <TableName>"

To find the log size of all database

DBCC SQLPERF ( LOGSPACE ) 

Fetch row count of all the tables in single query from a database

USE <DataBaseName>
GO
SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name
, SCHEMA_NAME(t.schema_id) AS schema_name
, t.name AS table_name, 
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2 
--and SCHEMA_NAME(t.schema_id) = 'dbo'

Bulk Insert

bulk insert dbo.TestTable from 'D:\SSISFiles\Test.csv'
with (
firstrow = 2,
FIELDTERMINATOR = ',' ,
ROWTERMINATOR = '\n'
);
GO

T-SQL to identify long running queries

Below queries helps to identify long running query and hanging queries:

USE MASTER
GO
SELECT SPID,ER.percent_complete,

CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
DATEADD(second,estimated_completion_time/1000, getdate()) as est_completion_time,

/* End of Article Code */

ER.command,ER.blocking_session_id, SP.DBID,LASTWAITTYPE, 

DB_NAME(SP.DBID) AS DBNAME, 

SUBSTRING(est.text, (ER.statement_start_offset/2)+1,
((CASE ER.statement_end_offset
WHEN -1 THEN DATALENGTH(est.text)
ELSE ER.statement_end_offset
END - ER.statement_start_offset)/2) + 1) AS QueryText,
TEXT,CPU,HOSTNAME,LOGIN_TIME,LOGINAME,
SP.status,PROGRAM_NAME,NT_DOMAIN, NT_USERNAME
FROM SYSPROCESSES SP
INNER JOIN sys.dm_exec_requests ER
ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(er.sql_handle) EST
ORDER BY CPU DESC

// This code has been referred from an article.

How to convert Integer value to Time in sql server

Declare @T int

set @T = 10455836

select (@T / 1000000) % 100 as hour,

       (@T / 10000) % 100 as minute,

       (@T / 100) % 100 as second,

       (@T % 100) * 10 as millisecond

select dateadd(hour, (@T / 1000000) % 100,

       dateadd(minute, (@T / 10000) % 100,

       dateadd(second, (@T / 100) % 100,

       dateadd(millisecond, (@T % 100) * 10, cast('00:00:00' as time(2)))))) 

How to Find Tables With Primary and Foreign Key Constraint in Database

Table with ForiegnKey constraint

USE EDW; --Database name
GO
SELECT f.name AS ForeignKey,
OBJECT_NAME(f.parent_object_id) AS TableName,
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS ColumnName,
OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

Table with PrimaryKey constraint


USE EDW;  --Database name
GO
SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

If Exists Logic

If exists logic for a Database

IF EXISTS(select * from sys.databases where name='yourDBname')

If exists logic for a table

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DimPACodes]') AND type in (N'U'))

If exists logic for a synonms

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N's_DimPACodes') AND type in (N'SN'))

If exists logic for a View

IF  EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'vw_DimProgram') AND type in (N'V'))

If exists logic for a column

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Dimprogram' and COLUMN_NAME = 'IAPKey')

If exists logic for a Schema

IF NOT EXISTS(SELECT * FROM sys.schemas where name = N'Test')

EXEC ('CREATE SCHEMA [telligent] Authorization [dbo]')

GO

(OR)

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'Test')

EXEC ('CREATE SCHEMA [Test] Authorization [dbo]')

GO

Interpreting type codes in sys.objects in SQL Server

AF = Aggregate function (CLR)
C  = CHECK constraint
D  = DEFAULT (constraint or stand-alone)
F  = FOREIGN KEY constraint
FN = SQL scalar function
FS = Assembly (CLR) scalar-function
FT = Assembly (CLR) table-valued function
IF = SQL inline table-valued function
IT = Internal table
P  = SQL Stored Procedure
PC = Assembly (CLR) stored-procedure
PG = Plan guide
PK = PRIMARY KEY constraint
R  = Rule (old-style, stand-alone)
RF = Replication-filter-procedure
S  = System base table
SN = Synonym
SQ = Service queue
TA = Assembly (CLR) DML trigger
TF = SQL table-valued-function
TR = SQL DML trigger
TT = Table type
U  = Table (user-defined)
UQ = UNIQUE constraint
V  = View
X  = Extended stored procedure

Script to check the last run status of job

SQL script to check the last run status of a job. This code will retrieve the last run status.


ECLARE @last_run_outcome INT 
SET @last_run_outcome = ( 
SELECT MAX(sjs.last_run_outcome) 
FROM msdb.dbo.sysjobs_view sjv 
INNER JOIN msdb.dbo.sysjobsteps sjs ON sjs.job_id = sjv.job_id 
WHERE name in ('MDR20') 
AND last_run_outcome = 0) 

--SELECT @last_run_outcome  

IF @last_run_outcome = 0 
EXEC msdb.dbo.sp_stop_job N'MDR20' 

Below is the outcome of the job for the last time ran:

0 = Failed
1 = Succeeded
3 = Canceled
5 = Unknown

SQL Geometry Data Type example

Using CHARINDEX in SQL Query

CHARINDEX returns a starting position of a string which helps developer to split a data based on the requirement.


DECLARE @AMT Varchar(50) = '$50K - $70K'
SELECT REPLACE(SUBSTRING('$50K - $70K',charindex('$', '$50K - $70K')+1, charindex('K', @AMT)),'K','') AS MinRange
,REPLACE(SUBSTRING('$50K - $70K',charindex('-', '$50K - $70K')+3, charindex('K', @AMT)),'K','') As MaxRange

Result:

MinRange         MaxRange
    50                      70

Get Hierarchial Level of Employee Using SQL

Create a new table as shown below:

CREATE TABLE [dbo].[Employee](
                    [EmployeeID] [int] NOT NULL,
                    [EmpName] [varchar](100) NULL,
                    [Department] [varchar](100) NULL,
                    [Designation] [varchar](100) NULL,
                    [Salary] [money] NULL,
                    [ManagerID] [int] NULL
    ) ON [PRIMARY]

Insert valuse to the table as required and apply the below script:

SELECT L3.EmpName AS LEVEL2, L2.EmpName LEVEL1, L1.EmpName LEVEL0
    ,CAST(ISNULL(L3.EmployeeID,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L2.EmployeeID,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L1.EmployeeID,'') AS VARCHAR(5)) AS LEVELPATH
,CAST(ISNULL(L3.EmpName,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L2.EmpName,'') AS VARCHAR(5))+'/' +
CAST(ISNULL(L1.EmpName,'') AS VARCHAR(5)) AS EMPLOYETREE
FROM Employee L1
LEFT JOIN Employee L2 ON L1.ManagerID = L2.EmployeeID
LEFT JOIN Employee L3 ON L2.ManagerID = L3.EmployeeID
LEFT JOIN Employee L4 ON L3.ManagerID = L4.EmployeeID
LEFT JOIN Employee L5 ON L4.ManagerID = L5.EmployeeID

Below is the result:

Configure Change Data Capture (CDC) Parameters in SQL Server

After enabling CDC in SQL Server, We can modify the retention period and the number of transactions that to be handled in Change Data Capture table.

Before configureing one should understand the basic terms in CDC Configuration:

Polling interval – (Default 5 seconds) the amount of time to wait between log scans

Maxtrans – (Default 500) the number of transactions to grab with each scan

Maxscans – (Default 10) the number of scans performed between each polling interval

Retention – (Default 72 hours, 4320 mins, 3 days). 

The period for which the new/updated/deleted data have to be retrieved and displayed in CDC tables. 

Execute the below query to get the CDC configured values:

SELECT * from msdb.dbo.cdc_jobs

Execute the below query to change capture instances:

EXEC sys.sp_cdc_change_job @job_type = 'capture'

       ,@maxtrans = 501

       ,@maxscans = 10

       ,@continuous = 1

       ,@pollinginterval = 5

Execute the below query to change retention period:

EXEC sys.sp_cdc_change_job @job_type = 'cleanup'

                          ,@retention = 4320 -- Number of minutes to retain (72 hours)

                          ,@threshold = 5000

Using this method we can use CDC hold the required period of historical data, i.e., for last 1 month, last 1 year or last 10 days, etc. 

Convert the year, month and day column as date format

Below is the script to convert the year, month and day column as date format

SELECT
CAST(
CAST(year AS VARCHAR(4)) +
RIGHT('0' + CAST(month AS VARCHAR(2)),2) +
RIGHT('0' + CAST(day AS VARCHAR(2)),2)
AS DATE )
FROM [dbo].[SampleTable]