USE master

 SELECT TOP 10
       REPLACE(CONVERT(VARCHAR(20), CONVERT(MONEY,qs.total_worker_time / qs.execution_count/1000),1),'.00','') as [Avg CPU Time(ms)]
      ,qs.execution_count
      ,substring (qt.text,
                  qs.statement_start_offset/2+1,
                  (case when qs.statement_end_offset = -1 then len (convert (nvarchar(max), qt.text)) * 2
                        else qs.statement_end_offset end - qs.statement_start_offset)/2) as query_text
      ,qt.dbid
      ,sd.name
      ,qt.objectid
      ,qt.number
      ,qt.encrypted
      ,qt.text
      ,sr.session_id
      ,sr.command
      ,sr.status
      ,sr.last_wait_type
      ,sr.wait_resource
      ,sq.query_plan
      ,fs.text
  FROM sys.dm_exec_query_stats qs
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
       CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as sq
       LEFT OUTER JOIN sys.databases as sd on qt.dbid = sd.database_id
       LEFT OUTER JOIN sys.dm_exec_requests as sr on qs.sql_handle = sr.sql_handle
       OUTER APPLY sys.fn_get_sql(sr.sql_handle) AS fs      
 ORDER BY qs.total_worker_time / qs.execution_count DESC
GO

 

SELECT
    te.name AS eventtype
    ,t.loginname
    ,t.spid
    ,t.starttime
    ,t.objectname
    ,t.databasename
    ,t.hostname
    ,t.ntusername
    ,t.ntdomainname
    ,t.clientprocessid
    ,t.applicationname 
 ,t.*
FROM sys.fn_trace_gettable
(
    CONVERT
    (VARCHAR(150)
    ,(
        SELECT TOP 1
            value
        FROM sys.fn_trace_getinfo(NULL) 
        WHERE property = 2
    )),DEFAULT
) T
INNER JOIN sys.trace_events as te
    ON t.eventclass = te.trace_event_id
--WHERE eventclass=164
where T.ObjectName='프로시저명'

 

흔히 테이블 명칭은 SQL Management 에서 테이블 디자이너로 수정을 하게되지만

sp_rename 프로시저를 이용하는 방법도 있다.

EXEC sp_rename '테이블명.컬럼명','바꿀이름','COLUMN'

ex) test_jun 테이블의 NAME 컬럼의 이름을 NAME2 로변경하고자 할때

​exec sp_rename 'test_jun.NAME' ,'NAME2','COLUMN' 

 

+ Recent posts