SunnyDB

select *, DATEPART(DW, CONVERT(DATETIME, '20190101')) 요일_숫자--요일을 숫자로 반환(일요일:1~토요일:7) select *, DATENAME(DW, CONVERT(DATETIME, '20190101')) 요일_한글--요일을 한글로 반환(일요일~토요일)

ALTER FUNCTION [dbo].[N2PosCd] (@nbar numeric)RETURNS varchar(13)ASBEGIN DECLARE @Ret varchar(13); SET @Ret = REPLACE(STR(@nbar, 13), SPACE(1), '0'); RETURN @Ret;END

ALTER FUNCTION [dbo].[to_checkdigita] (@v_pos_code varchar(20)) RETURNS varchar(20)as begin declare @v_checkdigit varchar(20), @v_pos_code2 varchar(20), @v_idx integer, @v_sum integer, @v_mok integer, @nLoop integer select @v_idx = 0, @v_sum = 0, @v_mok = 0, @nLoop = 0 select @v_checkdigit = '', @v_pos_code = RTrim(Ltrim(@v_pos_code)) if charindex('.', @v_pos_code) > 0 RETURN @v_pos_code + @v_ch..

ALTER FUNCTION [dbo].[to_checkdigit] (@v_pos_code varchar(20)) RETURNS varchar(20)as begin declare @v_checkdigit varchar(20), @v_idx integer, @v_sum integer, @v_mok integer, @nLoop integer select @v_idx = 0, @v_sum = 0, @v_mok = 0, @nLoop = 0 if charindex('.', @v_pos_code) > 0 RETURN @v_checkdigit if IsNumeric(@v_pos_code) = 0 RETURN @v_checkdigit if ( Len(@v_pos_code) % 2 = 1 ) set @v_pos_code ..

ALTER PROCEDURE [dbo].[TDESC] (@vTname varchar(50))ASBEGIN select a.name TABNAME, b.NAME COLNAME, d.name CTYPE, b.max_length STRLEN, LTRIM(Str(b.precision)) + ',' +LTRIM(Str(b.scale)) NUMLEN, c.VALUE ADESC from sys.all_objects a left outer join sys.all_columns b on (a.object_id = b.object_id) left outer join sys.extended_properties c on (b.object_id = c.major_id and b.column_id=c.minor_id) left ..

USE [Database명]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: -- Create date: -- Description: -- =============================================ALTER PROCEDURE [dbo].[MYDB_REINDEX]ASBEGIN Declare @vCount Int; select a.name INAME, b.name TNAME INTO #TEMPIDX from ( select distinct id, name from sys.sysindexes where name like 'PK_%' or name l..

USE [Database명]GO/****** 개체: UserDefinedFunction [dbo].[GetChoSung2] 스크립트 날짜: 04/26/2012 08:48:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE FUNCTION [dbo].[GetChoSung2] (@vStr nvarchar(200)) RETURNS nvarchar(200)as begin declare @vLen integer, @vPos integer; declare @vCh nvarchar(1), @vRCh nvarchar(1); declare @vRet nvarchar(200); SET @vLen = Len(@vStr); SET @vPos = 0; SET @vRet..

USE [DB명]GODBCC SHRINKFILE (N'NMTempDat', 10)GO

SELECT 'select * from ' + A.NAME, C.NAME FROM SYS.SYSOBJECTS A INNER JOIN SYS.SYSINDEXES B ON (A.ID = B.ID) INNER JOIN SYS.FILEGROUPS C ON (B.GROUPID = C.DATA_SPACE_ID) WHERE XTYPE = 'U'-- and c.name = 'TEMPDAT'ORDER BY C.NAME

net stop sqlserver net start sqlserver /m exec sp_dboption 'DB명', 'single_user', false dbcc checkdb('DB명', REPAIR_REBUILD) dbcc checkdb('DB명')

backup log DB명 with no_logdbcc shrinkdatabase(DB명, truncateonly)

드래그 복사 후 붙여넣기 - Database명(KSDB 를 사용하는 Database명으로) 변경 후 실행USE KSDB;GO-- Truncate the log by changing the database recovery model to SIMPLE.ALTER DATABASE KSDBSET RECOVERY SIMPLE;GO-- Shrink the truncated log file to 1 MB.DBCC SHRINKFILE (KSDB_log, 1);GO-- Reset the database recovery model.ALTER DATABASE KSDBSET RECOVERY FULL;GO

MS-SQL Management Studio 쿼리 창에서sp_who 입력 후 실행 또는 sp_who2 입력 후 실행 여러 사용자가 같은 DB, 같은 테이블을 Select 또는 Update가 동시에 진행하게 되는 경우블락 됨... 블락된 사용자나 Process ID를 찾아 kill 시켜 블락해제에 활용 함...