미련곰 블로그

-- SP 에서 내용 찾기
SELECT A.name
 , B.text
FROM  dbo.sysobjects AS A
INNER JOIN dbo.syscomments AS B ON A.id = B.id
WHERE A.xtype  = 'P' -- SP
AND  A.category = '0'
AND  B.text LIKE '%내용%'

CREATE FUNCTION [dbo].[UF_RegularExText]
(
	@Value NVARCHAR(50)
)
RETURNS NVARCHAR(2000)
AS 
BEGIN
	DECLARE @Rtn NVARCHAR(2000)
	DECLARE @Temp NVARCHAR(2)
	DECLARE @SerchValue NVARCHAR(20)
	DECLARE @i INT
	DECLARE @n INT
	
	SET @Rtn = ''
	SET @i = 1
	SET @n = LEN(@Value)
	
	WHILE @i <= @n
	BEGIN
		SET @Temp = Substring(@Value, @i, 1)
		
		SET @SerchValue = (CASE @Temp 
							WHEN 'ㄱ' THEN '[가-깋]'
							WHEN 'ㄲ' THEN '[까-낗]'
							WHEN 'ㄴ' THEN '[나-닣]'
							WHEN 'ㄷ' THEN '[다-딯]'
							WHEN 'ㄸ' THEN '[따-띻]'
							WHEN 'ㄹ' THEN '[라-맇]'
							WHEN 'ㅁ' THEN '[마-밓]'
							WHEN 'ㅂ' THEN '[바-빟]'
							WHEN 'ㅃ' THEN '[빠-삫]'
							WHEN 'ㅅ' THEN '[사-싷]'
							WHEN 'ㅆ' THEN '[싸-앃]'
							WHEN 'ㅇ' THEN '[아-잏]'
							WHEN 'ㅈ' THEN '[자-짛]'
							WHEN 'ㅉ' THEN '[짜-찧]'
							WHEN 'ㅊ' THEN '[차-칳]'
							WHEN 'ㅋ' THEN '[카-킿]'
							WHEN 'ㅌ' THEN '[타-팋]'
							WHEN 'ㅍ' THEN '[파-핗]'
							WHEN 'ㅎ' THEN '[하-힣]'
							ELSE @Temp END)
							
		SET @Rtn = RTRIM(@Rtn) + @SerchValue
		
		SET @i = @i + 1
	END
	
	Return @Rtn	
	
	

	SELECT * 
	FROM Table 
	WHERE PATINDEX(UF_RegularExText('ㄱ') + '%' , Column) > 0

	-- yyyy-MM-dd 
	CONVERT(NVARCHAR(10), GETDATE(), 126)	
	
	-- yyyy-MM-ddTHH:mm	
	CONVERT(NVARCHAR(16), GETDATE(), 126)	
	
	-- yyyy-MM-dd HH:mm
	CONVERT(NVARCHAR(16), GETDATE(), 120)	
	
	-- yyyyMMdd
	CONVERT(NVARCHAR(8), GETDATE(), 112)	
	
	-- 해당월의 시작일
	DATEADD(d, (1 - DAY(GETDATE())), GETDATE())		
	
	-- 해당월의 마지막일
	DATEADD(MS, -3, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0))
	

SELECT distinct(OBJECT_NAME(id)) AS SpNm 
FROM syscomments WHERE text like '%문자열%'

SELECT * 
FROM sys.objects
WHERE type = 'P'
AND CONVERT(varchar(10), modify_date, 102) 
    = CONVERT(varchar(10), GETDATE(), 102)

SELECT 
CAST((CAST('1' AS FLOAT) / CAST('10' AS FLOAT) * 100) AS INT)

declare @date char(8)
set @date = convert(char(8),getdate(),112)

select a.서버명,@date as 년월일,a.백업한횟수,b.권장백업갯수
from 
(
select 
  @@servername 서버명,count(distinct database_name) as 백업한횟수
  from msdb.dbo.backupset with(nolock)
  where database_name not in ('model','tempdb') and type = 'D'
  and backup_finish_date > convert(datetime,'' + @date + '')
) as a 
 inner join 
( 
  select @@servername as 서버명,count(*)  as '권장백업갯수'
  from sys.databases 
  where name not in ('model','tempdb') 
) as b
on a.서버명 = b.서버명

	
	SELECT * INTO 백업table FROM 원본table