|
|
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 , text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle)st JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle |
|
select year as '년', month as '월', count(*) as count from ( select YEAR(us.fd_dtcreate) as 'year', MONTH(us.fd_dtcreate) as 'month' from tb_ykfile as us where us.fd_dtCreate > '2020-08-01' and us.fd_dtCreate <= '2022-01-01' ) tb group by year, month order by year, month ; |
|
SELECT CONVERT(varCHAR(10),Dateadd(d, a.number,'2020-08-01 00:00:00'),120) AS dt ,Isnull(b.count, 0) AS count FROM master..spt_values a LEFT JOIN ( SELECT count(*) AS count, CONVERT(CHAR(10), b1.fd_dtcreate,120) AS fd_dtcreate FROM tb_ykfile b1 WITH(nolock) GROUP BY CONVERT(CHAR(10), b1.fd_dtcreate, 120) ) b ON CONVERT(CHAR(10), Dateadd(d, a.number,'2020-08-01 00:00:00'), 120) = b.fd_dtcreate WHERE TYPE = 'P' AND CONVERT(CHAR(10), DATEADD(D, NUMBER, '2020-08-01'), 120) <= '2022-01-01' order by dt ; |
|
SELECT row_number() OVER(ORDER BY fd_nIdService DESC) AS PagedNumber, * FROM [tb_Service] AS [t] WHERE 1=1 ORDER BY [fd_nIdService] DESC OFFSET ((2 - 1) * 10) ROWS FETCH NEXT 10 ROWS ONLY |
|
(10%) select name from sysobjects where type='U' (13%) select * from information_schema.tables (76%) select * from sys.tables |
[한글컬럼값 들어있는 것만 지우기]
|
select * from tbl where field like '%[가-힣]%' delete from tbl where field like '%[가-힣]%' |
[영문컬럼값 들어있는 것만 지우기]
|
select * from tbl where field like '%[A-Za-z]%' delete from tbl where field like '%[A-Za-z]%' |
출처 : http://nextstep.co.kr/105
— 백업생성(있으면 생략)
|
BACKUP DATABASE Northwind TO DISK = 'c:\Northwind.bak' |
–백업정보확인(논리적파일명, 물리적저장위치및파일명 확인)
|
RESTORE FILELISTONLY FROM DISK = 'c:\Northwind.bak' |
— TestDB라는 새로운 데이터베이스로 새로운 경로(C:\temptest*.*)으로 복원
|
RESTORE DATABASE TestDB FROM DISK = 'c:\Northwind.bak' WITH MOVE 'Northwind' TO 'c:\testtestdb.mdf', MOVE 'Northwind_log' TO 'c:\testtestdb.ldf' |
[출처] MSSQL 테이블명 , 컬럼명 바꾸기|작성자 zino1187
1.테이블명 바꾸기
|
sp_rename OldTable , NewTable |
2.컬럼명 바꾸기
|
sp_rename 'TableName.OldColumn','TableName.NewColumn' |
[출처] mssql alter table column작성자 재원맨
1. 테이블 필드 수정하기
|
alter table sale alter column title varchar(200) not null |
2. 필드 삭제하기
|
alter table sale drop title |
3. 필드 추가하기
|
alter table sal add sale varchar(20) not null alter table sale add isDel bit DEFAULT 0 not null |
|
"C:\Program Files\Microsoft SQL Server90\Tools\Binn\sqlcmd" -S localhost -i e:\Backupbackup.sql |
풀백업
|
declare @runtime nvarchar(10) declare @backupdevice nvarchar(100) declare @backupname nvarchar(100) set @runtime = substring(convert(nvarchar(20), getdate(), 8) ,1,2) set @backupdevice = N'e:\Backupbackup_' + @runtime + N'.bak' set @backupname = N'backup_' + @runtime BACKUP DATABASE [TU] TO DISK = @backupdevice WITH NOFORMAT, INIT, NAME = @backupname, SKIP, NOREWIND, NOUNLOAD, STATS = 10 |
증분 백업
|
declare @runtime nvarchar(10) declare @backupdevice nvarchar(100) declare @backupname nvarchar(100) set @runtime = substring(convert(nvarchar(20), getdate(), 8) ,1,2) set @backupdevice = N'e:\Backupbackup_' + @runtime + N'.bak' set @backupname = N'backup_' + @runtime BACKUP DATABASE [TU] TO DISK = @backupdevice WITH DIFFERENTIAL, NOFORMAT, INIT, NAME = @backupname, SKIP, NOREWIND, NOUNLOAD, STATS = 10 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
|
private OleDbDataReader rd; private OleDbConnection Conn; private string strRet; private OleDbCommand cmd; private StringBuilder sbSQL; private OleDbParameter paramnSeq; try { // SQLOLEDB MSDAORA Conn = new OleDbConnection("Provider=MSDAORA;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.xxx )(PORT = 1521)))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = GROUP))); User Id =; Password =;"); Conn.Open(); try { sbSQL = new StringBuilder(); sbSQL.Append(" SELECT * "); sbSQL.Append(" FROM HEIS_CUSTOMER "); sbSQL.Append(" WHERE 1=1 "); sbSQL.Append(" AND CUSTOMER_CODE = ? "); paramnSeq = new OleDbParameter("CUSTOMER_CODE", OleDbType.Char, 4); paramnSeq.Value = "0003"; cmd = new OleDbCommand(sbSQL.ToString(), Conn); cmd.Parameters.Add(paramnSeq); rd = cmd.ExecuteReader(); if (rd.Read()) { strRet = rd["CUSTOMER_CODE"].ToString(); } else { strRet = ""; } } catch (System.Exception e1) { throw e1; } finally { rd.Close(); } } catch (System.Exception e2) { throw e2; } finally { Conn.Close(); } |
|
|