var _gaq = _gaq || []; _gaq.push(['_setAccount', 'UA-333696-1']); _gaq.push(['_trackPageview']); _gaq.push(['_trackPageLoadTime']); (function() { var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true; ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js'; var s = document.getElementsByTagName('script')[0]; s.parentNode.insertBefore(ga, s); })();
  • 2007年07月16日

    Microsoft SQL Server管理常用的SQL和T-SQL

    分类:

    1.     查看数据库的版本      

    select @@version
     
    常见的几种SQL SERVER打补丁后的版本号:
     8.00.194  Microsoft SQL Server 2000
     8.00.384  Microsoft SQL Server 2000 SP1
     8.00.532  Microsoft SQL Server 2000 SP2
     8.00.760  Microsoft SQL Server 2000 SP3
     8.00.818  Microsoft SQL Server 2000 SP3 w/ Cumulative Patch MS03-031
     8.00.2039  Microsoft SQL Server 2000 SP4 
          
    Microsoft SQL Server 2005
    9.00.1399.06 (Intel X86) 
    9.00.2047.00 (Intel X86)(Build 3790: Service Pack 1)
          

    2.     查看数据库所在机器操作系统参数 

    exec master..xp_msver
          

    3. 查看数据库启动的参数sp_configure
           

    4. 查看SQLSERVER2000数据库启动时间       
        select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
          
        
    查看SQLSERVER2005数据库启动时间       
        select convert(varchar(30),start_time,120) from master.sys.dm_exec_requests where session_id=1
          
        
    查看数据库服务器名和实例名
        print 'Server Name...............: ' + convert(varchar(30),@@SERVERNAME)       
        print 'Instance..................: ' + convert(varchar(30),@@SERVICENAME)     

    5. 查看所有数据库名称及大小 sp_helpdb
          
        
    重命名数据库用的SQL
        sp_renamedb 'old_dbname', 'new_dbname'
          

    6. 查看所有数据库用户登录信息

       sp_helplogins


        
    查看所有数据库用户所属的角色信息 

       sp_helpsrvrolemember
          
       
    修复迁移服务器时孤立用户时,可以用的LoneUser脚本或者fix_all_orphan_user过程
          
        
    更改某个数据对象的用户属主
         sp_changeobjectowner [@objname=] 'object', [@newowner=] 'owner'
          
        
    注意: 更改对象名的任一部分都可能破坏脚本和存储过程。
          
        
    把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本
          
        
    查看某数据库下,对象级用户权限

    sp_helprotect
          

    7. 查看链接服务器          
        sp_helplinkedsrvlogin
          
        
    查看远端数据库用户登录信息      
        sp_helpremotelogin
          

    8.查看某数据库下某个数据对象的大小
       sp_spaceused @objname
        
       
    还可以用sp_toptables过程看最大的N(默认为50)个表
        
       
    查看某数据库下某个数据对象的索引信息
        sp_helpindex @objname
          
        
    还可以用SP_NChelpindex过程查看更详细的索引情况
        SP_NChelpindex @objname
          
        clustered
    索引是把记录按物理顺序排列的,索引占的空间比较少。
        
    对键值DML操作十分频繁的表我建议用非clustered索引和约束,fillfactor参数都用默认值。
       
        
    查看某数据库下某个数据对象的的约束信息
        sp_helpconstraint @objname
        

    9.查看数据库里所有的存储过程和函数
       use @database_name
       sp_stored_procedures

        
    查看存储过程和函数的源代码
        sp_helptext '@procedure_name'
          
       
    查看包含某个字符串@str的数据对象名称
        select distinct object_name(id) from syscomments where text like '%@str%'
        
        
    创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数
        
       
    解密加密过的存储过程和函数可以用sp_decrypt过程
        

    10.查看数据库里用户和进程的信息
        sp_who

       查看SQL Server数据库里的活动用户和进程的信息
        sp_who 'active'

        
    查看SQL Server数据库里的锁的情况
         sp_lock
          
        
    进程号1--50SQL Server系统内部用的,进程号大于50的才是用户的连接进程.

        spid
    是进程编号,dbid是数据库编号,objid是数据对象编号

        
    查看进程正在执行的SQL语句
        dbcc inputbuffer ()
                    
       
    推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句
        sp_who3
          
        
    检查死锁用sp_who_lock过程
          sp_who_lock          
                 

    11.查看和收缩数据库日志文件的方法
         
       
    查看所有数据库日志文件大小         
        dbcc sqlperf(logspace)

       
    如果某些日志文件较大,收缩简单恢复模式数据库日志,收缩后@database_name_log的大小单位为M
         backup log @database_name with no_log
         dbcc shrinkfile (@database_name_log, 5)

    12.分析SQL Server SQL 语句的方法:
        set statistics time {on | off}
        set statistics io {on | off}

       
    图形方式显示查询执行计划
       
    在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L或者点击工具栏里的图形
       
       
    文本方式显示查询执行计划
         set showplan_all {on | off}
         set showplan_text { on | off }
         set statistics profile { on | off }
          

    13.出现不一致错误时,NT事件查看器里出3624号错误,修复数据库的方法
        
    先注释掉应用程序里引用的出现不一致性错误的表,然后在备份或其它机器上先恢复然后做修复操作
         alter database [@error_database_name] set single_user
        
        
    修复出现不一致错误的表
          dbcc checktable('@error_table_name',repair_allow_data_loss)
        
        
    或者可惜选择修复出现不一致错误的小型数据库名
         dbcc checkdb('@error_database_name',repair_allow_data_loss)
         alter database [@error_database_name] set multi_user

         CHECKDB
    3个参数:
         repair_allow_data_loss
    包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误,
        
    以及删除已损坏的文本对象,这些修复可能会导致一些数据丢失。
        
    修复操作可以在用户事务下完成以允许用户回滚所做的更改。
        
    如果回滚修复,则数据库仍会含有错误,应该从备份进行恢复。
        
    如果由于所提供修复等级的缘故遗漏某个错误的修复,则将遗漏任何取决于该修复的修复。
        
    修复完成后,请备份数据库。

         repair_fast
    进行小的、不耗时的修复操作,如修复非聚集索引中的附加键。
        
    这些修复可以很快完成,并且不会有丢失数据的危险。

         repair_rebuild
    执行由 repair_fast 完成的所有修复,包括需要较长时间的修复(如重建索引)。
        
    执行这些修复时不会有丢失数据的危险。

     

    14.  行列倒置

     

    Create Procedure colsToRows

           @tblName      varchar(50),   --name of table

           @Cond           varchar(6000) --limiting condition for record selection

                                --any valid where clause without the where ie id=75 or id between 1 and 5

                                --I would recommend that you not return more than 100 rows

     

    AS

     

    declare @colName varchar(50), @colId smallint

    declare    @sql varchar(1000)

     

    --Begin selection

    select @colName='',@colId=1

     

    create table #tmp (cName varchar(50))

    --add value columns for number of records returned

     

    Select @sql = 'Declare @cols smallint, @sql varchar(7000);Select @cols=count(*) From ' + @tblName

    If Len(@cond)>0

           select @sql = @sql + ' Where ' + @cond

     

    select @sql = @sql + ';

           while @cols > 0

           begin

                  select @sql = ''alter table #tmp add [Val'' + cast(@cols as varchar) + ''] varchar(50)''

                  Exec(@sql)

                  select @cols = @cols - 1

           End'

     

    exec( @sql)

     

    set nocount on

    while @colname is not null

    begin

           select @colname = col_name(object_id(@tblName),@colID)

           Select @colID = @colid + 1

           select @sql = 'declare @retVal varchar(6000);set @retVal = '''';

                  update ' + @tblName + ' set @retVal=@retVal + '','' + substring(cast (' + @colname + ' as varchar),1,50)'

           if len(@cond) > 0

                  select @sql = @sql + ' Where ' + @cond

           select @sql = @sql + ';

                  select @retval = replace(substring(@retVal,2,len(@retVal)),'','','''''','''''')

                  Select @retVal = '''''''' + @retVal + ''''''''

                  declare @sql varchar(7000);

                  select @sql = ''Insert Into #tmp Values('''''+ @colName + ''''','' + @retVal + '')''

           if len(@sql) > 0 Exec (@sql)

    '

           execute (@sql)

    end

     

    Select * from #tmp

    drop table #tmp

    GO

     

    /*

    Usage

    colsToRows 'SomeTable', 'Some Condition'

    */

     

    附录SQL ServerDatatime类型为什么不能早于1753 年?

    Good question. There are historical reasons for this limitation. In what we sometimes refer to as the "Western world," there have been two calendars in modern times: the Julian and Gregorian calendars. These calendars were a number of days apart (depending on which century you looked at), so when a culture that used the Julian calendar moved to the Gregorian calendar, it removed from 10 to 13 days. Great Britain made this shift in 1752. (So, in that year, September 2, 1752 was followed by September 14, 1752.)
    An educated guess as to why Sybase SQL Server—the predecessor of Microsoft SQL Server—selected 1753 as the earliest date is that if you were to store a date earlier than 1753, you would also have to know which country was using which calendar and also handle this 10- to 13-day jump. So Sybase decided to not allow dates earlier than 1753. Note, too, that other countries made the shift later than 1752. Turkey, for instance, did it in 1927.
    Being Swedish, I find it amusing that Sweden had the weirdest implementation. Sweden originally planned to skip every February 29, leap day, over a period of 40 years (from 1700 to 1740) so that Sweden would be in sync with the Gregorian calendar after 1740 (but meanwhile not in sync with anyone else). However, in 1704 and 1708 the leap day wasn't skipped for some reason, so in 1712 (which was a leap year), Sweden inserted yet one more extra day (imagine being born in Feb 30!) and then made the shift over a day, in 1753, in a similar manner to everyone else.

     

     

    资源:

    1.      SQL Server 管理常用的SQLT-SQL

    2.      SQLServerCentral.com

    3.      Change Result Set Columns to Rows

    4.      Compare the data of 2 Tables

    分享到:

    历史上的今天:

    UNIX介绍 2007年07月16日