richardbasile

alter session force parallel dml parallel 16;
alter session force parallel query parallel 16;

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
    PROCEDURE migrateTable(sourceTable varchar2) IS
        interimTable varchar2(30) := 'XX' || substr(sourceTable, '3');

        str varchar2(1000);

richardbasile

create login <user name> with password = '<password>', default_database = <db name>;
create user <user name> from login <user name> with default_schema = dbo;

alter role <role> add member <user name>;
grant showplan to <user name>;

richardbasile

ALTER USER <user name> WITH PASSWORD = '<new password>' OLD_PASSWORD = '<old password>'

richardbasile

SELECT total_physical_memory_kb/1024 as "Physical Memory MB"
     , available_physical_memory_kb/1024 as "Available Memory MB"
     , available_physical_memory_kb/(total_physical_memory_kb*1.0)*100 AS "% Memory Free"
  FROM sys.dm_os_sys_memory

richardbasile

declare @Time_Start datetime = getdate()-7;
declare @Time_End datetime = getdate();
-- Create the temporary table
DECLARE @ErrorLog TABLE (logdate datetime
                      , processinfo varchar(255)
                      , Message varchar(500))
-- Populate the temporary table
INSERT @ErrorLog (logdate, processinfo, Message)
   EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N'desc';
-- Filter the temporary table

richardbasile

SELECT d.name AS "Database"
     , ISNULL(CONVERT(VARCHAR, b.backupdate, 120), 'NEVER') AS "Last Full Backup"
  FROM sys.databases d
  LEFT JOIN (
           SELECT database_name, type, MAX(backup_finish_date) AS backupdate
             FROM msdb.dbo.backupset
            WHERE type LIKE 'D'
            GROUP BY database_name, type
       ) b on d.name=b.database_name
 WHERE (backupdate IS NULL OR backupdate < getdate()-1)

richardbasile

SELECT spid, kpid, blocked, d.name as dbname, open_tran, status, hostname, cmd, login_time, loginame, net_library
  FROM sys.sysprocesses p
  LEFT JOIN sys.databases d ON p.dbid=d.database_id
 ORDER BY d.name, loginame

richardbasile

DECLARE @sp_who2 TABLE(
        SPID INT,
        Status VARCHAR(MAX),
        LOGIN VARCHAR(MAX),
        HostName VARCHAR(MAX),
        BlkBy VARCHAR(MAX),
        DBName VARCHAR(MAX),
        Command VARCHAR(MAX),
        CPUTime INT,
        DiskIO INT,

richardbasile

SELECT t.name AS [TableName]
     , i.name AS [IndexName]
     , i.is_primary_key
     , i.is_unique_constraint
     , i.type_desc
     , SUM(s.[used_page_count]) * 8 AS IndexSizeKB
  FROM sys.indexes i
  JOIN sys.tables t ON t.object_id = i.object_id
  JOIN sys.dm_db_partition_stats s ON s.object_id = i.object_id AND s.index_id = i.index_id
 GROUP BY t.name 

richardbasile

SELECT t.name as table_name, i.name as index_name, s.index_type_desc, s.avg_fragmentation_in_percent, s.page_count
  FROM sys.dm_db_index_physical_stats (
         DB_ID('<db name>')
       , NULL --OBJECT_ID('dbo.<table name>')
       , NULL
         -- NULL to view all indexes;
         -- otherwise, input index number
       , NULL -- NULL to view all partitions of an index
       , NULL -- 'DETAILED') as s
  join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id