SQL Server my FAQ

一些sqlserver常用的sql或命令:

1.how to desc object
  Select * From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'TABLENAME'
  exec sp_columns test

 
2.how to get sqls current running:  
   select r.session_id, status,qt.text ,qt.dbid,qt.objectid,r.cpu_time,r.total_elapsed_time,r.reads,r.writes,r.logical_reads, r.scheduler_id
    from sys.dm_exec_requests r
    cross apply sys.dm_exec_sql_text(sql_handle) as qt
    order by r.scheduler_id, r.status, r.session_id
    
3.how to Find Most Expensive Queries Using DMV
   SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
     ((CASE qs.statement_end_offset
     WHEN -1 THEN DATALENGTH(qt.TEXT)
     ELSE qs.statement_end_offset
     END - qs.statement_start_offset)/2)+1),
     qs.execution_count,
     qs.total_logical_reads, qs.last_logical_reads,
     qs.total_logical_writes, qs.last_logical_writes,
     qs.total_worker_time,
     qs.last_worker_time,
     qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
     qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
     qs.last_execution_time,
     qp.query_plan
     FROM sys.dm_exec_query_stats qs
     CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
     CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
     ORDER BY qs.total_logical_reads DESC -- logical reads
     -- ORDER BY qs.total_logical_writes DESC -- logical writes
     -- ORDER BY qs.total_worker_time DESC -- CPU time    
     
4.  how to get recent run cached sqls and their execute_plan
    SELECT deqs.last_execution_time AS Time, dest.TEXT AS [Query], deqs.sql_handle, deqs.plan_handle
    FROM sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
    ORDER BY deqs.last_execution_time DESC
    
    select * from sys.dm_exec_query_plan(plan_handle)
       example: select * from sys.dm_exec_query_plan(0x06000700F4E88F0440E1D887000000000000000000000000)

5. current connection informations
    exec sp_who
    exec sp_who2
    
    select * from master..sysprocesses where hostname<>''   
    
    sp_who可以返回如下信息： (可选参数LoginName, 或active代表活动会话数)
    Spid         (系统进程ID)
    status      (进程状态)
    loginame  (用户登录名)
    hostname(用户主机名)
    blk           (阻塞进程的SPID)
    dbname   (进程正在使用的数据库名)
    Cmd        (当前正在执行的命令类型)

    sp_who2除了显示上面sp_who的输出信息外，还显示下面的信息：  (可选参数LoginName, 或active代表活动会话数)
    CPUTime           (进程占用的总CPU时间)
    DiskIO              (进程对磁盘读的总次数)
    LastBatch         (客户最后一次调用存储过程或者执行查询的时间)
    ProgramName  (用来初始化连接的应用程序名称，或者主机名)
     
    sp_who3是某人自定义的存储过程，(可选参数spid)，显示“非系统会话，且是活动的会话”的详细情况。
         CREATE PROCEDURE sp_who3  
        (  @SessionID int = NULL )  
        AS
        BEGIN
        SELECT
            SPID                = er.session_id  
            ,Status             = ses.status  
            ,[Login]            = ses.login_name  
            ,Host               = ses.host_name  
            ,BlkBy              = er.blocking_session_id  
            ,DBName             = DB_Name(er.database_id)  
            ,CommandType        = er.command  
            ,SQLStatement       = st.text  
            ,ObjectName         = OBJECT_NAME(st.objectid)  
            ,ElapsedMS          = er.total_elapsed_time  
            ,CPUTime            = er.cpu_time  
            ,IOReads            = er.logical_reads + er.reads  
            ,IOWrites           = er.writes  
            ,LastWaitType       = er.last_wait_type  
            ,StartTime          = er.start_time  
            ,Protocol           = con.net_transport  
            ,ConnectionWrites   = con.num_writes  
            ,ConnectionReads    = con.num_reads  
            ,ClientAddress      = con.client_net_address  
            ,Authentication     = con.auth_scheme  
        FROM sys.dm_exec_requests er  
        OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st  
        LEFT JOIN sys.dm_exec_sessions ses  
        ON ses.session_id = er.session_id  
        LEFT JOIN sys.dm_exec_connections con  
        ON con.session_id = ses.session_id  
        WHERE er.session_id > 50  
            AND @SessionID IS NULL OR er.session_id = @SessionID  
        ORDER BY
            er.blocking_session_id DESC
            ,er.session_id  
        END

        go
    
    Other views:
        select * from sys.dm_exec_connections
        select * from sys.dm_exec_sessions

6. how to get current session information, and latest sql according to sessionid @@spid
    
    SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name'
    
    SELECT @@SPID
    GO
    
    dbcc inputbuffer(@@SPID)
        dbcc inputbuffer(64)
        
    There are several ways to find out what is the latest run query from system table sys.sysprocesses.
        DECLARE @sqltext VARBINARY(128)
        SELECT @sqltext = sql_handle
        FROM sys.sysprocesses
        WHERE spid = 61
        SELECT TEXT
        FROM sys.dm_exec_sql_text(@sqltext)
        GO
7.  Do I need to use GO statements between batches?
        They're not strictly required - they're just instructions for the SQL Server Management Studio to execute the statements up to this point now and then keep on going. GO is not a T-SQL keyword or anything - it's just an instruction that works in SSMS.
    Sometimes, you need a GO - e.g. if you add a column to a table, and then want to select it again, you need to have a GO between the adding of the column, and the query of it.
    E.g. if you try to execute this, you'll get errors from SSMS:
    ALTER TABLE (sometable) ADD DateTimeStamp DATETIME
    SELECT ID, DateTimeStamp FROM (sometable) WHERE ID > 5
     Results in:
    Msg 207, Level 16, State 1, Line 9 Invalid column name 'datetimestamp'.
     
    The point is: SSMS is trying to verify the whole statement at once, but on the SELECT statement, it will complain about the missing DateTimeStamp column.
    ALTER TABLE (sometable) ADD DateTimeStamp DATETIME
    GO       
    SELECT ID, DateTimeStamp FROM (sometable) WHERE ID > 5
     If you put a GO between the two statements, it'll work, because SSMS won't parse and verify the whole statement ahead of time - it will do the first part, and then only parse the second (after the GO).
    But other than situations like this one, GO is hardly ever required.

 
8. How to check who are the owner of specific database
    USE test
　　GO
　　EXEC sp_helpuser
　　GO
9. create new table based on know table:
   select * into person.address_test from person.Address
    
    
10. general sys tables    
    sys.databases,  sys.tables, sys.indexes, sys.index_columns,
 
11. How to get all user tables row count
    SELECT o.name AS "Table Name", i.rowcnt AS "Row Count"
    FROM sysobjects o, sysindexes i
    WHERE i.id = o.id
    AND i.indid IN(0,1)
    AND o.xtype = 'u' --只统计用户表
    AND o.name <> 'sysdiagrams'
    ORDER BY i.rowcnt DESC --按行排降序

    COMPUTE SUM(i.rowcnt), count(o.name); --汇总行数，表数
    GO