query « 어느용

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

sql 스크립트 생성시에 옵션

MyBatis.Net – SQL 쿼리 이쁘게(?) 출력

iBatis.Net에서 쿼리를 로그로 출력하면 죽~ 한줄로만 나온다.

이때 가독성을 높이기 위해서 iBatis.Net의 코드에서 아래를 변경한다.

DataMapper 프로젝트의 DomSqlMapBuilder 클래스에서 ParseDynamicTags() 메소드…

//string data = child.InnerText.Replace('n', ' ').Replace('r', ' ').Replace('t', ' '); // 주석처리!!
string data = child.InnerText.Replace('t', ' ');

1 2	//string data = child.InnerText.Replace('n', ' ').Replace('r', ' ').Replace('t', ' '); // 주석처리!! string data = child.InnerText.Replace('t', ' ');

2008-10-24 11:39:24,281 - Statement Id: [select_categories] Prepared SQL:
[select CategoryID,
CategoryName,
Description
from Categories]

2008-10-24 11:39:24,281 - Statement Id: [select_categories] Prepared SQL:

[select CategoryID,

CategoryName,

Description

from Categories]

오라클에서 테이블 명들을 가지고 오는 쿼리

select *
from dba_tab_comments
where 1=1
and owner='anyxxxxxx'
and comments is NULL
and table_name not like 'BIN$%'
order by table_name

select *

from dba_tab_comments

where 1=1

and owner='anyxxxxxx'

and comments is NULL

and table_name not like 'BIN$%'

order by table_name

select 시에 세로행 출력을 가로행 출력으로 변환

출처 : http://bomb.egloos.com/1073673

SELECT
	MAX(DECODE(A, '1', B) as COL1,
	MAX(DECODE(A, '2', B) as COL2,
	MAX(DECODE(A, '3', B) as COL3
FROM
(
	SELECT A, B FROM [table]
)

SELECT

MAX(DECODE(A, '1', B) as COL1,

MAX(DECODE(A, '2', B) as COL2,

MAX(DECODE(A, '3', B) as COL3