MSSQL-常用命令

SELECT @@VERSION ;

SELECT name from DRMS_Logging_rms_sunac_local_443.sys.tables ; 
select * from sys.tables ;
select * from sys.views ;

SELECT * FROM Master..SysDatabases ORDER BY dbid ;

SELECT * FROM dbname.dbo.sysfiles ;

EXEC sp_spaceused 
@updateusage = 'TRUE' ;

select GETDATE()

SELECT   s.session_id, 
            r.status, 
            r.blocking_session_id                                 'Blk by', 
            r.wait_type, 
            wait_resource, 
            r.wait_time / (1000.0)                             'Wait Sec', 
            r.cpu_time, 
            r.logical_reads, 
            r.reads, 
            r.writes, 
            r.total_elapsed_time / (1000.0)                    'Elaps Sec', 
            Substring(st.TEXT,(r.statement_start_offset / 2) + 1, 
                    ((CASE r.statement_end_offset 
                        WHEN -1 
                        THEN Datalength(st.TEXT) 
                        ELSE r.statement_end_offset 
                        END - r.statement_start_offset) / 2) + 1) AS statement_text, 
            Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid,st.dbid)) + N'.' + Quotename(Object_name(st.objectid,st.dbid)), 
                    '') AS command_text, 
            r.command, 
            s.login_name, 
            s.host_name, 
            s.program_name, 
            s.last_request_end_time, 
            s.login_time, 
            r.open_transaction_count 
FROM     sys.dm_exec_sessions AS s 
              JOIN sys.dm_exec_requests AS r 
            ON r.session_id = s.session_id 
            CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st 
--WHERE    r.session_id != @@SPID 
ORDER BY r.cpu_time desc, r.status, 
            r.blocking_session_id, 
            s.session_id 

SELECT st.text, qp.query_plan, qs.*
  FROM (SELECT TOP 50 *
          FROM sys.dm_exec_query_stats
         ORDER BY total_worker_time DESC) AS qs
 CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
 CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
 WHERE qs.max_worker_time > 300
    OR qs.max_elapsed_time > 300

select 
  ssp.name,ssp.type_desc,ssp.is_disabled,ssl.is_expiration_checked,ssl.is_policy_checked 
from 
  sys.server_principals ssp 
left join  
  sys.sql_logins ssl
on 
  ssp.principal_id = ssl.principal_id 
order by 2,3;

  select 
        'alter table ' + quotename(schema_name(schema_id)) + '.' +
        quotename(object_name(parent_object_id)) +
        ' drop constraint '+quotename(name) + ';'
    from sys.foreign_keys

--以最小模式启动SQLServer实例，修改内存限制 
--考虑到有多个应用从不同的服务器连接本SQLServer实例，首先做一些外围处理（禁止应用程序连接本实例），避免它们干扰修复过程 
--– 关闭本地服务器（连接该实例）的应用程序，
--– 对于跨服务器访问的，在防火墙中关闭SQLServer端口 


--1.开启一个cmd窗口 窗口1，-f最小模式启动实例 
CMD> cd /D C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe 

CMD> sqlservr.exe -f -sMSSQLSERVER
--MSSQLSERVER 默认实例名，可修改为你实际实例名修改 

--2.cmd窗口2 （窗口1运行后）立即运行下面命令进入 命令行模式 
CMD> sqlcmd -E -sMSSQLSERVER -S localhost,11433 
--修复，就本故障来说，修改内存最大值为不限制， 
  EXEC sp_configure 'show advanced options', '1' RECONFIGURE WITH OVERRIDE;
  EXEC sp_configure 'max server memory', 2147483647 RECONFIGURE WITH OVERRIDE;
  EXEC sp_configure 'show advanced options', '0' RECONFIGURE WITH OVERRIDE;
  GO

SELECT 
  session_id as SPID, command, a.text AS Query, start_time, percent_complete, 
  dateadd(second,estimated_completion_time/1000, getdate()) as estimated_completion_time 
FROM 
  sys.dm_exec_requests r 
CROSS APPLY 
  sys.dm_exec_sql_text(r.sql_handle) a 
WHERE 
  r.command in ('BACKUP DATABASE','RESTORE DATABASE')