Author: Vinay

Load excel files into a table on SQL server database

$File = "\\NFS01\SQLBackup\DATA_SQL.xlsx"
$Instance = "SQLSERVER01"
$Database = "dbname01"

$fileName =  [System.IO.Path]::GetFileNameWithoutExtension($File)

foreach($sheet in Get-ExcelSheetInfo $File)
{

$data = Import-Excel -Path $File -WorksheetName $sheet.name | ConvertTo-DbaDataTable
$tablename = "table_name"

Write-DbaDataTable -SqlInstance $Instance -Database $Database -InputObject $data -Truncate -Table $tablename

}

Update an existing database mail account using T-SQL

EXEC msdb..sysmail_update_account_sp @account_name = 'SMTP_database_mail',            -- sysname
                                     @email_address = N'altersemail@example.com',            -- nvarchar(128)
                                     @display_name = N'Database Mail',             -- nvarchar(128)
                                     @replyto_address = N'altersemail@example.com',          -- nvarchar(128)
                                     @description = N'Primary database alerts profile',              -- nvarchar(256)
                                     @mailserver_name = 'smtp.office365.com',         -- sysname
                                     @port = 587,                       -- int
                                     @username = 'altersemail@example.com',                -- sysname
                                     @password = 'SecurePassword',                -- sysname
                                     @use_default_credentials = 0, -- bit
                                     @enable_ssl = 1

List Redshift tables, views and their owners

SELECT n.nspname AS schema_name
 , pg_get_userbyid(c.relowner) AS table_owner
 , c.relname AS table_name
 , CASE WHEN c.relkind = 'v' THEN 'view' ELSE 'table' END 
   AS table_type
 , d.description AS table_description
 FROM pg_class As c
 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
 LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
 LEFT JOIN pg_description As d 
      ON (d.objoid = c.oid AND d.objsubid = 0)
 WHERE c.relkind IN('r', 'v') 
ORDER BY n.nspname, c.relname ;

Get table creation date in Redshift

SELECT
TRIM(nspname) AS schema_name,
TRIM(relname) AS table_name,
relcreationtime AS creation_time
FROM pg_class_info
LEFT JOIN pg_namespace ON pg_class_info.relnamespace = pg_namespace.oid
WHERE reltype != 0
AND TRIM(nspname) = 'schemanamehere'
and TRIM(relname) = 'tablenamehere';