SQL Server Queries

### Modify column type

```
ALTER TABLE ZAWTH_Raw_Core_Branch.raw.POS_TRANSACTION ALTER COLUMN [transaction_line_id] VARCHAR(128) NULL;
```

### Check currently running queries
reference: https://stackoverflow.com/a/29400789
```
SELECT   SPID       = er.session_id
    ,STATUS         = ses.STATUS
    ,[Login]        = ses.login_name
    ,Host           = ses.host_name
    ,BlkBy          = er.blocking_session_id
    ,DBName         = DB_Name(er.database_id)
    ,CommandType    = er.command
    ,ObjectName     = OBJECT_NAME(st.objectid)
    ,CPUTime        = er.cpu_time
    ,StartTime      = er.start_time
    ,TimeElapsed    = CAST(GETDATE() - er.start_time AS TIME)
    ,SQLStatement   = st.text
FROM    sys.dm_exec_requests er
    OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
    LEFT JOIN sys.dm_exec_sessions ses
    ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
    ON con.session_id = ses.session_id
WHERE   st.text IS NOT NULL
```

### Check currently running jobs
reference: https://stackoverflow.com/a/54684796
```
 WITH
    CTE_Sysession (AgentStartDate)
    AS 
    (
        SELECT MAX(AGENT_START_DATE) AS AgentStartDate FROM MSDB.DBO.SYSSESSIONS
    )   
SELECT sjob.name AS JobName
        ,CASE 
            WHEN SJOB.enabled = 1 THEN 'Enabled'
            WHEN sjob.enabled = 0 THEN 'Disabled'
            END AS JobEnabled
        ,sjob.description AS JobDescription
        ,CASE 
            WHEN ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NULL  THEN 'Running'
            WHEN ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NOT NULL AND HIST.run_status = 1 THEN 'Stopped'
            WHEN HIST.run_status = 0 THEN 'Failed'
            WHEN HIST.run_status = 3 THEN 'Canceled'
        END AS JobActivity
        ,DATEDIFF(MINUTE,act.start_execution_date, GETDATE()) DurationMin
        ,hist.run_date AS JobRunDate
        ,run_DURATION/10000 AS Hours
        ,(run_DURATION%10000)/100 AS Minutes 
        ,(run_DURATION%10000)%100 AS Seconds
        ,hist.run_time AS JobRunTime 
        ,hist.run_duration AS JobRunDuration
        ,'tulsql11\dba' AS JobServer
        ,act.start_execution_date AS JobStartDate
        ,act.last_executed_step_id AS JobLastExecutedStep
        ,act.last_executed_step_date AS JobExecutedStepDate
        ,act.stop_execution_date AS JobStopDate
        ,act.next_scheduled_run_date AS JobNextRunDate
        ,sjob.date_created AS JobCreated
        ,sjob.date_modified AS JobModified      
            FROM MSDB.DBO.syssessions AS SYS1
        INNER JOIN CTE_Sysession AS SYS2 ON SYS2.AgentStartDate = SYS1.agent_start_date
        JOIN  msdb.dbo.sysjobactivity act ON act.session_id = SYS1.session_id
        JOIN msdb.dbo.sysjobs sjob ON sjob.job_id = act.job_id
        LEFT JOIN  msdb.dbo.sysjobhistory hist ON hist.job_id = act.job_id AND hist.instance_id = act.job_history_id
        WHERE ACT.start_execution_date IS NOT NULL AND ACT.stop_execution_date IS NULL
        ORDER BY ACT.start_execution_date DESC
```

### Check indexes usage

reference: https://www.mssqltips.com/sqlservertip/1239/how-to-get-index-usage-information-in-sql-server/

```
SELECT
    OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME], 
    I.[NAME] AS [INDEX NAME], 
    USER_SEEKS, 
    USER_SCANS, 
    USER_LOOKUPS, 
    USER_UPDATES 
FROM
    SYS.DM_DB_INDEX_USAGE_STATS AS S 
    INNER JOIN SYS.INDEXES AS I 
    ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID 
WHERE
    OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
    AND OBJECT_NAME(S.[OBJECT_ID]) like 'table_name_prefix%';
```

### Create temp table

```
drop table if exists dbo.temp_gyu_1;
select top 100 * 
into dbo.temp_gyu_1
from [ZAWTW_RDW_branch].[dbo].[FactSalesTransaction];
```

### Delete all tables in database

```
DECLARE @DATABASE_NAME VARCHAR(100);
DECLARE @TABLE_NAME VARCHAR(500);
DECLARE @SCHEMA_NAME VARCHAR(100);

DECLARE curLoadKeys CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
SELECT DISTINCT TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA IN ('dim', 'fact', 'working', 'recyclebin') OPEN curLoadKeys;
FETCH NEXT
FROM curLoadKeys INTO @DATABASE_NAME,
    @SCHEMA_NAME,
    @TABLE_NAME;
WHILE @@FETCH_STATUS = 0 BEGIN;
DECLARE @FULL_NAME VARCHAR(500) = CONCAT(
        @DATABASE_NAME,
        '.',
        @SCHEMA_NAME,
        '.',
        @TABLE_NAME
    );
print (@FULL_NAME);
EXEC spDropTable @FULL_NAME, 0;
FETCH NEXT
FROM curLoadKeys INTO @DATABASE_NAME,
    @SCHEMA_NAME,
    @TABLE_NAME;
END;
CLOSE curLoadKeys;
DEALLOCATE curLoadKeys;
```

### Calculate database sizes

```
USE [Admin];

SET NOCOUNT ON;

EXEC spDropTable '#DatabaseSizes',0;
CREATE TABLE #DatabaseSizes
(
    DatabaseName        SYSNAME   NULL
  , TotalSpaceGB        MONEY  NULL
  , UsedSpaceGB         MONEY  NULL
);
DECLARE @NL                 NCHAR(2) = NCHAR(13) + NCHAR(10);

DECLARE @SQL                NVARCHAR(MAX);
DECLARE @FileGroupName      SYSNAME = 'DATA';
DECLARE @ParmDefinition     NVARCHAR(500) = N'@FileGroupName SYSNAME';

DECLARE C1 CURSOR
READ_ONLY
FOR
    SELECT name
    FROM sys.databases
    WHERE name NOT LIKE '%JenCI%'
      AND name NOT LIKE '%JenkinsCI%'
      AND LEFT(name, 2) <> 'DW'
      AND name NOT IN ('Admin', 'XSR_Adhoc', 'XSRDataTools', 'msdb', 'master', 'model', 'tempdb');

DECLARE @DatabaseName varchar(200)
OPEN C1

FETCH NEXT FROM C1 INTO @DatabaseName
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN

SET @SQL =
              'USE ' + @DatabaseName + ';'                                                                              + @NL
            + 'INSERT INTO #DatabaseSizes (DatabaseName, TotalSpaceGB, UsedSpaceGB)'                                    + @NL
            + 'SELECT ''' + @DatabaseName + ''''                                                                        + @NL
            + '    , CurrentSizeMB = CONVERT(MONEY,sum(sf.size / 128.0)) / 1024'                                        + @NL
            + '    , CurrentUsedMB'                                                                                     + @NL
            + '      = CONVERT(MONEY,sum(CAST(FILEPROPERTY(sf.name, ''SpaceUsed'') AS DECIMAL(24, 6)) / 128.0)) / 1024' + @NL
            + 'FROM'                                                                                                    + @NL
            + '    sys.database_files sf'                                                                               + @NL
            + '    INNER JOIN sys.filegroups fg ON fg.data_space_id = sf.data_space_id'                                 + @NL
            + 'WHERE'                                                                                                   + @NL
            + '    sf.type = 0      -- Data files and not log files, full-text or FILETREAM'                            + @NL
            + 'AND'                                                                                                     + @NL
            + '    fg.name = @FileGroupName'                                                                            + @NL
            + 'GROUP BY'                                                                                                + @NL
            + '    fg.name;'                                                                                            + @NL;

EXEC sp_executesql @SQL,
                           @ParmDefinition,
                           @FileGroupName = @FileGroupName;
	END
	FETCH NEXT FROM C1 INTO @DatabaseName
END

CLOSE C1
DEALLOCATE C1

SELECT * FROM #DatabaseSizes ORDER BY TotalSpaceGB DESC;
```