整理的一些T-sql（二）

-- Demo 1: 
use  northwind 
go  
set   statistics  IO  on  
go  
select   count ( * )  from  northwind.dbo.employees 
go  
set   statistics  IO  off  
go  

use  northwind 
go  
exec  sp_spaceused employees 
go  

-- Demo 2: 
set   statistics  time  on  
go  
select   count ( * )  from  northwind.dbo.employees 
go  
set   statistics  time  off  
go  

-- Demo 3: 
set  showplan_text  on  
go  
select   count ( * )  from  northwind.dbo.employees 
go  
set  showplan_text  off  
go  

-- Demo 4: 
set  nocount  on  
go  
select   count ( * )  from  northwind.dbo.employees 
go  
set  nocount  off  
go  


-- Demo 5查询单条sql语句的执行时间: 
declare   @start_time   datetime  
select   @start_time = getdate () 
select   *   from  northwind.dbo.employees 
select   ' 查询语句的执行时间(毫秒) ' = datediff (ms, @start_time , getdate ()) 

-- Demo 6查询成批的sql语句的执行时间: 
create   table  #save_time(start_time  datetime   not   null ) 
insert  #save_time  values ( getdate ()) 
go  
select   *   from  employees 
go  
select   *   from  orders 
go  
select   ' 查询语句的执行时间(毫秒) ' = datediff (ms,start_time, getdate ()) 
from  #save_time 
drop   table  #save_time 
go  

-- Demo 7返回语句的执行计划内容: 
set  showplan_all  on  
go  
select   *   from  pubs.dbo.authors 
go  
set  showplan_all  off  
go  

-- Demo 8从执行计划判断是否需要优化SQL: 
/**/ /*SEEK操作*/  
set  showplan_all  on  
go  
select   *   from  pubs.dbo.sales  where  stor_id >= ' 7131 '  
go  
set  showplan_all  off  
go  
/**/ /*SCAN操作*/  
set  showplan_all  on  
go  
select   *   from  pubs.dbo.sales  where  ord_date  is   not   null  
go  
set  showplan_all  off  
go  

-- Demo 9连接查询VS子查询: 
/**/ /*子查询*/  
set   statistics  io  on  
go  
select  au_fname,au_lname  from  pubs.dbo.authors  where  au_id  in  
( select  au_id  from  pubs.dbo.titleauthor) 
set   statistics  io  off  
go  


/**/ /*连接查询*/  
set   statistics  io  on  
go  
select   distinct  au_fname,au_lname  from  pubs.dbo.authors  as  a  inner   join  
pubs.dbo.titleauthor  as  t  on  a.au_id = t.au_id 
go  
set   statistics  io  off  
go  

-- Demo 10智能优化: 
select  p1.productname  from  northwind.dbo.products  as  p1  inner   join  
northwind.dbo.products  as  p2  on  (p1.unitprice = p2.unitprice) 
where  p2.productname  like   ' Alice% '  
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   
    
2 . 查看数据库所在机器操作系统参数     
    exec  master..xp_msver 
    
3 . 查看数据库启动的参数         
   sp_configure 
         
4 . 查看数据库启动时间         
    select   convert ( varchar ( 30 ),login_time, 120 )  from  master..sysprocesses  where  spid = 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 
    
   修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程 
    
   更改某个数据对象的用户属主 
   sp_changeobjectowner  [ @objectname = ]   ' 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 -- 50是SQL 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 完成的所有修复，包括需要较长时间的修复（如重建索引）。 
  执行这些修复时不会有丢失数据的危险。  

 MS - SQL数据库开发常用汇总  1 .按姓氏笔画排序:
Select   *   From  TableName  Order   By  CustomerName Collate Chinese_PRC_Stroke_ci_as 
2 .数据库加密:
select  encrypt( ' 原始密码 ' )
select  pwdencrypt( ' 原始密码 ' )
select  pwdcompare( ' 原始密码 ' , ' 加密后密码 ' )  =   1 -- 相同；否则不相同 encrypt('原始密码')
select  pwdencrypt( ' 原始密码 ' )
select  pwdcompare( ' 原始密码 ' , ' 加密后密码 ' )  =   1 -- 相同；否则不相同
3 .取回表中字段:
declare   @list   varchar ( 1000 ), @sql   nvarchar ( 1000 ) 
select   @list = @list + ' , ' + b.name  from  sysobjects a,syscolumns b  where  a.id = b.id  and  a.name = ' 表A '
set   @sql = ' select  ' + right ( @list , len ( @list ) - 1 ) + '  from 表A '  
exec  ( @sql )
4 .查看硬盘分区:
EXEC  master..xp_fixeddrives
5 .比较A,B表是否相等:
if  ( select  checksum_agg(binary_checksum( * ))  from  A)
=
( select  checksum_agg(binary_checksum( * ))  from  B)
print   ' 相等 '
else
print   ' 不相等 '
6 .杀掉所有的事件探察器进程:
DECLARE  hcforeach  CURSOR  GLOBAL  FOR   SELECT   ' kill  ' + RTRIM (spid)  FROM  master.dbo.sysprocesses
WHERE  program_name  IN ( ' SQL profiler ' ,N ' SQL 事件探查器 ' )
EXEC  sp_msforeach_worker  ' ? '
7 .记录搜索:
开头到N条记录
Select   Top  N  *   From  表
-- -----------------------------
N到M条记录(要有主索引ID)
Select   Top  M - N  *   From  表  Where  ID  in  ( Select   Top  M ID  From  表)  Order   by  ID  Desc
-- --------------------------------
N到结尾记录
Select   Top  N  *   From  表  Order   by  ID  Desc
8 .如何修改数据库的名称:
sp_renamedb  ' old_name ' ,  ' new_name '  
9 ：获取当前数据库中的所有用户表
select  Name  from  sysobjects  where  xtype = ' u '   and  status >= 0
10 ：获取某一个表的所有字段
select  name  from  syscolumns  where  id = object_id ( ' 表名 ' )
11 ：查看与某一个表相关的视图、存储过程、函数
select  a. *   from  sysobjects a, syscomments b  where  a.id  =  b.id  and  b. text   like   ' %表名% '
12 ：查看当前数据库中所有存储过程
select  name  as  存储过程名称  from  sysobjects  where  xtype = ' P '
13 ：查询用户创建的所有数据库
select   *   from  master..sysdatabases D  where  sid  not   in ( select  sid  from  master..syslogins  where  name = ' sa ' )
或者
select  dbid, name  AS   DB_NAME   from  master..sysdatabases  where  sid ＜ >   0x01
14 ：查询某一个表的字段和数据类型
select  column_name,data_type  from  information_schema.columns
where  table_name  =   ' 表名 '  
[ n ] . [ 标题 ] :
Select   *   From  TableName  Order   By  CustomerName 
[ n ] . [ 标题 ] :

快速获取表test的记录总数 : 
select  rows  from  sysindexes  where  id  =   object_id ( ' test ' )  and  indid  in  ( 0 , 1 )
提取数据库内所有表的字段详细说明的SQL语句 :

SELECT  
( case   when  a.colorder = 1   then  d.name  else   ''   end ) N ' 表名 ' , 
a.colorder N ' 字段序号 ' , 
a.name N ' 字段名 ' , 
( case   when   COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1   then   ' √ ' else   ''  
end ) N ' 标识 ' , 
( case   when  ( SELECT   count ( * ) 
FROM  sysobjects 
WHERE  (name  in  
( SELECT  name 
FROM  sysindexes 
WHERE  (id  =  a.id)  AND  (indid  in  
( SELECT  indid 
FROM  sysindexkeys 
WHERE  (id  =  a.id)  AND  (colid  in  
( SELECT  colid 
FROM  syscolumns 
WHERE  (id  =  a.id)  AND  (name  =  a.name)))))))  AND  
(xtype  =   ' PK ' )) > 0   then   ' √ '   else   ''   end ) N ' 主键 ' , 
b.name N ' 类型 ' , 
a.length N ' 占用字节数 ' , 
COLUMNPROPERTY (a.id,a.name, ' PRECISION ' )  as  N ' 长度 ' , 
isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 )  as  N ' 小数位数 ' , 
( case   when  a.isnullable = 1   then   ' √ ' else   ''   end ) N ' 允许空 ' , 
isnull (e. text , '' ) N ' 默认值 ' , 
isnull (g. [ value ] , '' )  AS  N ' 字段说明 '  
FROM  syscolumns a 
left   join  systypes b 
on  a.xtype = b.xusertype 
inner   join  sysobjects d 
on  a.id = d.id  and  d.xtype = ' U '   and  d.name <> ' dtproperties '  
left   join  syscomments e 
on  a.cdefault = e.id 
left   join  sysproperties g 
on  a.id = g.id  AND  a.colid  =  g.smallid 
order   by   object_name (a.id),a.colorder
获取表结构 [ 把 'sysobjects' 替换 成 'tablename' 即可 ]  

SELECT   CASE   IsNull (I.name,  '' ) 
When   ''   Then   ''  
Else   ' * '  
End   as  IsPK, 
Object_Name (A.id)  as  t_name, 
A.name  as  c_name, 
IsNull ( SubString (M. text ,  1 ,  254 ),  '' )  as  pbc_init, 
T.name  as  F_DataType, 
CASE   IsNull ( TYPEPROPERTY (T.name,  ' Scale ' ),  '' ) 
WHEN   ''   Then   Cast (A.prec  as   varchar ) 
ELSE   Cast (A.prec  as   varchar )  +   ' , '   +   Cast (A.scale  as   varchar ) 
END   as  F_Scale, 
A.isnullable  as  F_isNullAble 
FROM  Syscolumns  as  A 
JOIN  Systypes  as  T 
ON  (A.xType  =  T.xUserType  AND  A.Id  =   Object_id ( ' sysobjects ' ) ) 
LEFT   JOIN  ( SysIndexes  as  I 
JOIN  Syscolumns  as  A1 
ON  ( I.id  =  A1.id  and  A1.id  =   object_id ( ' sysobjects ' )  and  (I.status  &   0x800 )  =   0x800   AND  A1.colid  <=  I.keycnt) ) 
ON  ( A.id  =  I.id  AND  A.name  =   index_col ( ' sysobjects ' , I.indid, A1.colid) ) 
LEFT   JOIN  SysComments  as  M 
ON  ( M.id  =  A.cdefault  and   ObjectProperty (A.cdefault,  ' IsConstraint ' )  =   1  ) 
ORDER   BY  A.Colid  ASC
四种方法取表里n到m条纪录：

1 .
select   top  m  *   into  临时表(或表变量)  from  tablename  order   by  columnname  --  将top m笔插入
set   rowcount  n
select   *   from  表变量  order   by  columnname  desc
2 .
select   top  n  *   from  ( select   top  m  *   from  tablename  order   by  columnname) a  order   by  columnname  desc
3 .如果tablename里没有其他identity列，那么：
select   identity ( int ) id0, *   into  # temp   from  tablename
取n到m条的语句为：
select   *   from  # temp   where  id0  >= n  and  id0  <=  m
如果你在执行  select   identity ( int ) id0, *   into  # temp   from  tablename这条语句的时候报错,那是因为你的DB中间的select  into / bulkcopy属性没有打开要先执行：
exec  sp_dboption 你的DB名字, ' select into/bulkcopy ' ,true


4 .如果表里有identity属性，那么简单：
select   *   from  tablename  where   identitycol   between  n  and  m 

-- ------------------------------------------------------------------------------

数据库管理常用SQL

1 . 查看数据库的版本 
select   @@version  

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

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

4 . 查看数据库启动时间 
select   convert ( varchar ( 30 ),login_time, 120 )  from  master..sysprocesses  where  spid = 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 

修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程 

更改某个数据对象的用户属主 
sp_changeobjectowner  [ @objectname = ]   ' object ' ,  [ @newowner = ]   ' owner '  

注意: 更改对象名的任一部分都可能破坏脚本和存储过程。 

把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本 

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 -- 50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程. 
spid是进程编号,dbid是数据库编号,objid是数据对象编号 
查看进程正在执行的SQL语句 
dbcc  inputbuffer () 

推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句 
sp_who3 

检查死锁用sp_who_lock过程 
sp_who_lock 

11 .收缩数据库日志文件的方法 
收缩简单恢复模式数据库日志，收缩后 @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 完成的所有修复，包括需要较长时间的修复（如重建索引）。 
执行这些修复时不会有丢失数据的危险。