JustinMcNamara74

Netezza Course Notes

Usage Course

Possible Issues

1. If you see "connection refused" do the following:

• Log in using nz creds
• Type nzstate to see if the connection is stopped
• If it is stopped, enter nzstart
• Log back into student account, and try accessing the nzsql again

JustinMcNamara74

USE [master]
GO

/****** Object:  UserDefinedFunction [dbo].[fn_hexadecimal]    
Credit - http://weblogs.sqlteam.com/billg/archive/2010/07/08/Scripting-out-SQL-Server-Logins.aspx
****/

SET ANSI_NULLS ON
GO

JustinMcNamara74

nvm install 5.*

nvm uninstall 6.*

npm install -g grunt-cli bower yo generator-karma generator-angular

JustinMcNamara74

-- Find all triggers in a database
-- http://stackoverflow.com/questions/4305691/need-to-list-all-triggers-in-sql-server-database-with-table-name-and-tables-sch

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 

JustinMcNamara74

SELECT name
FROM Envision.sys.tables
WHERE name LIKE '%ucker%'; --Search for Tucker, Sucker, Pucker... what were you thinking?

JustinMcNamara74

SELECT * FROM sys.indexes
WHERE object_id = (SELECT object_id FROM sys.objects WHERE name = 'tableName')

JustinMcNamara74

-- List all enabled jobs
USE [msdb]
select name
	,description
	,enabled
	,job_id
	,date_created
	,date_modified
	from dbo.sysjobs
where enabled = 1

JustinMcNamara74

/******************************************************************************
	Task: Move a DB to another location
	Resources:  https://msdn.microsoft.com/en-us/library/ms345483.aspx
*******************************************************************************/

Use master;
GO

/** -------
	Step 1

JustinMcNamara74

DECLARE @Gigs DECIMAL;
SET @Gigs = POWER(2,30);

-- http://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/

--------------------------------------------------------------------------------- 
--Database Backups for all databases For Previous Week 
--------------------------------------------------------------------------------- 
SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 

JustinMcNamara74

/**
A few notes: 
      - Remember the DB Master Key!!
      - When creating certificates/keys be descriptive, as there may be many on your instance of SQL Server
**/ 


 IF (select Count(*) from sys.symmetric_keys where name like '%DatabaseMasterKey%') = 0
 BEGIN
   CREATE master key Encryption by password = 'SomePassword';