성승규 nukeguys

## datetime.sql
-- https://docs.microsoft.com/ko-kr/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles
SELECT CONVERT(VARCHAR, GETDATE(), 120)

-- 지정된 두 날짜를 더하여 새 날짜를 반환한다.
SELECT DATENAME(날짜부분, 날짜)

-- 두 날짜 사이의 간격을 계산하여 지정된 단위(시간, 일, 주 등)로 반환한다.
SELECT DATEDIFF(날짜부분,시작날짜, 종료날짜)

-- 지정한 날짜 부분을 문자열로 반환한다.

## update_all_views_that_have_dependencies_on_a_changed_object.sql
SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects so INNER JOIN sys.sql_dependencies sd
ON so.object_id = sd.object_id
WHERE type = 'V'
AND sd.referenced_major_id = object_id('OBJECT_NAME')

## tip_mssql.sql
-- ref. https://msdn.microsoft.com/ko-kr/library/ms176007(v=sql.90).aspx
-- ref. sys.sql_dependencies

-- object의 정의
exec sp_helptext N'NAME'

-- object의 종속성 정보 in current DB
exec sp_depends N'NAME'

-- view의 메타데이터 업데이트

## inquire_sql_history.sql
-- ref. http://mkklab.tistory.com/7
-- Microsoft SQL Server Management Studio에서 직접 실행했던 쿼리들만 보여준다.

SELECT
	db_name(st.dbid) DBName,
	object_schema_name(objectid, st.dbid) SchemaName,
	object_name(objectid, st.dbid) SPName,
	qs.total_elapsed_time,
	creation_time,
	last_execution_time,

## tip_git.sh
# Delete Remote Branch
git push origin --delete <branch_name>
git push origin :<branch_name>

# To list all unstaged tracked changed files
git diff --name-only

# To list all staged tracked changed files
git diff --name-only --staged

## tip_proc.sql
-- procedure 상에서 decrypt
DBO.UF_DEC_MRM(휴대전화번호)
CONVERT(VARCHAR(100), DECRYPTBYKEY(휴대전화번호))

-- SDA 자료분석 작성시 parameter 제거
@DataSection1 VARCHAR(50) = NULL

## extract_sp_list.sql
/*
       SP추출
*/
--변수
DECLARE @SqlVersion AS CHAR(4)
DECLARE @maxi AS INT , @maxj AS INT
DECLARE @i AS INT , @j AS INT
DECLARE @Output VARCHAR(4000),@description VARCHAR(4000)

CREATE TABLE #Tables  (id int identity(1, 1), Object_id int, name varchar(155), type varchar(20), [definition] varchar(MAX))

## search_for_sp.sql
SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%찾을 내용%'
	-- https://docs.microsoft.com/ko-kr/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles
	SELECT CONVERT(VARCHAR, GETDATE(), 120)

	-- 지정된 두 날짜를 더하여 새 날짜를 반환한다.
	SELECT DATENAME(날짜부분, 날짜)

	-- 두 날짜 사이의 간격을 계산하여 지정된 단위(시간, 일, 주 등)로 반환한다.
	SELECT DATEDIFF(날짜부분,시작날짜, 종료날짜)

	-- 지정한 날짜 부분을 문자열로 반환한다.
	SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
	FROM sys.objects so INNER JOIN sys.sql_dependencies sd
	ON so.object_id = sd.object_id
	WHERE type = 'V'
	AND sd.referenced_major_id = object_id('OBJECT_NAME')
	-- ref. https://msdn.microsoft.com/ko-kr/library/ms176007(v=sql.90).aspx
	-- ref. sys.sql_dependencies

	-- object의 정의
	exec sp_helptext N'NAME'

	-- object의 종속성 정보 in current DB
	exec sp_depends N'NAME'

	-- view의 메타데이터 업데이트
	-- ref. http://mkklab.tistory.com/7
	-- Microsoft SQL Server Management Studio에서 직접 실행했던 쿼리들만 보여준다.

	SELECT
	db_name(st.dbid) DBName,
	object_schema_name(objectid, st.dbid) SchemaName,
	object_name(objectid, st.dbid) SPName,
	qs.total_elapsed_time,
	creation_time,
	last_execution_time,
	# Delete Remote Branch
	git push origin --delete <branch_name>
	git push origin :<branch_name>

	# To list all unstaged tracked changed files
	git diff --name-only

	# To list all staged tracked changed files
	git diff --name-only --staged
	-- procedure 상에서 decrypt
	DBO.UF_DEC_MRM(휴대전화번호)
	CONVERT(VARCHAR(100), DECRYPTBYKEY(휴대전화번호))

	-- SDA 자료분석 작성시 parameter 제거
	@DataSection1 VARCHAR(50) = NULL
	/*
	SP추출
	*/
	--변수
	DECLARE @SqlVersion AS CHAR(4)
	DECLARE @maxi AS INT , @maxj AS INT
	DECLARE @i AS INT , @j AS INT
	DECLARE @Output VARCHAR(4000),@description VARCHAR(4000)

	CREATE TABLE #Tables (id int identity(1, 1), Object_id int, name varchar(155), type varchar(20), [definition] varchar(MAX))
	SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
	FROM sys.procedures
	WHERE OBJECT_DEFINITION(object_id) LIKE '%찾을 내용%'