MSSQL « 어느용

MSSQL History

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

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

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 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 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

;

MSSQL Paging

          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

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

MSSQL Table List Query

(10%) select name from sysobjects where type='U'
(13%) select * from information_schema.tables
(76%) select * from sys.tables

(10%) select name from sysobjects where type='U'

(13%) select * from information_schema.tables

(76%) select * from sys.tables

MSSQL 한글 영문 구분

[한글컬럼값 들어있는 것만 지우기]

select *  from tbl where field like '%[가-힣]%'
delete  from tbl where field like '%[가-힣]%'

1 2	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]%'

1 2	select * from tbl where field like '%[A-Za-z]%' delete from tbl where field like '%[A-Za-z]%'

ms sql 데이타베이스 복원방법

출처 : http://nextstep.co.kr/105

— 백업생성(있으면 생략)

BACKUP DATABASE Northwind
TO DISK = 'c:\Northwind.bak'

1 2	BACKUP DATABASE Northwind TO DISK = 'c:\Northwind.bak'

–백업정보확인(논리적파일명, 물리적저장위치및파일명 확인)

RESTORE FILELISTONLY
FROM DISK = 'c:\Northwind.bak'

1 2	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'

RESTORE DATABASE TestDB

FROM DISK = 'c:\Northwind.bak'

WITH MOVE 'Northwind' TO 'c:\testtestdb.mdf',

MOVE 'Northwind_log' TO 'c:\testtestdb.ldf'

mssql alter table column (테이블 필드 수정, 삭제, 추가) 및 테이블명, 필드(컬럼)명 수정

[출처] MSSQL 테이블명 , 컬럼명 바꾸기|작성자 zino1187

1.테이블명 바꾸기

sp_rename OldTable , NewTable

1	sp_rename OldTable , NewTable

2.컬럼명 바꾸기

sp_rename 'TableName.OldColumn','TableName.NewColumn'

1	sp_rename 'TableName.OldColumn','TableName.NewColumn'

[출처] mssql alter table column작성자 재원맨

1. 테이블 필드 수정하기

alter table sale alter column title varchar(200) not null

1	alter table sale alter column title varchar(200) not null

2. 필드 삭제하기

alter table sale drop title

1	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

1 2	alter table sal add sale varchar(20) not null alter table sale add isDel bit DEFAULT 0 not null

sql server 백업을 하게 하기 위한 스크립트

"C:\Program Files\Microsoft SQL Server90\Tools\Binn\sqlcmd" -S localhost -i e:\Backupbackup.sql

1	"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 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

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

OLE DB로 oracle이건 MSSQL이건 데이터를 가져오기 위한 기초 코드

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();
}

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;

}