1 ---查询当前数据库一共有多少张表  2 select COUNT(1) from sysobjects where xtype=‘U‘  3   4 ---查询当前数据库有多少张视图  5 select COUNT(1) from sysobjects where xtype=‘V‘  6   7 ---查询当前数据库 有多少个存储过程  8 select COUNT(1) from sysobjects where xtype=‘P‘  9  10 -- row_number()函数用法 row_number() over (order by cloumn) from tableName 11 select ROW_NUMBER() over (order by cardNo ),* from T_ValueCard 12  13  14 select type from sysobjects group by type 15 -- 16 select * from sysobjects  17  18  19 --触发器 20 if not exists (select * from sysobjects where id=OBJECT_ID(‘Student‘) and name=‘Student‘) 21 begin 22     create table Student    --学生表 23     ( 24         StudentID int primary key,    --学号 25         StudentName nvarchar(15)    --姓名     26     ) 27 end 28  29 if not exists (select * from sysobjects where id=OBJECT_ID(‘BorrowRecord‘) and name=‘BorrowRecord‘) 30 begin 31     create table BorrowRecord    --借书记录表 32     ( 33         RecordID int primary key,    --流水号 34         StudentID int,                --学号 35         OutTime datetime,            --借出时间 36         ReturnDate datetime            --归还时间 37     ) 38 end 39  40 --创建触发器语法 41 --create trigger trigger_name            42  43 --on {table_name | view_name}            44  45 --{for | After | Instead of }            [ insert, update,delete ]            46  47 --as            sql_statement   48  49 --删除触发器  drop trigger trigger_name  50   51 -- 查看当前数据库的触发器 52 --select * from  sysobjects where  type=‘TR‘ 53 --查看某个触发器 54 --exec  sp_helptext ‘TStudent‘ 55  56 --触发器实例----触发器是属于隐式的 57 --create trigger TStudent 58 --on Student                --在Student表中创建触发器 59 --for Update                --针对什么事件触发 60 --as                        --时间触发后要做的事情 61 --if update(StudentID) 62 --begin 63 --    Update BorrowRecord  64 --        set StudentID=i.StudentID 65 --        from BorrowRecord br,Deleted d,Inserted i    --Deleted和Inserted为临时虚拟表 66 --        where br.StudentID=d.StudentID 67 --end 68 --go 69  70 --查询的时候临时生成自定义数据列 71 select ‘Web‘ as SourceName,* from TM_Goods  72 --生成空列数据 73 select ‘‘ as SourceName, * from TM_Goods 74  75  76  77 --- 所查列的值出现特殊结果字符 给这个结果字符加单引号 如下所示的Sex字段 78 select top 20 ID, GuestName,MemNo,CardNo, [Identity], BagNo,BoxNo,case Sex when ‘M‘ then ‘男‘ when ‘F‘ then ‘女‘ end as Sex,Holes,  79 case [Status] when 1 then ‘已预约‘ when 2 then ‘已来场登记‘ when 3 then ‘已球场登记‘ when 4 then ‘已出发‘ 80 when 5 then ‘已离场‘ when 6 then ‘已结账‘ when 7 then ‘退回结账‘ when -1 then ‘已取消预约‘ when -2 then ‘取消登记‘  81 when 0 then ‘未知‘ end as [Status],  82 StrategyCode,   CreateTime,CheckinTime, Creator, CheckOutTime, CheckoutUser, 83 LastEditTime, LastEditor ,ExpiryDate, MainCardNo, 84 SalemanCode from Checkin  85  86 ---查询的时候如果结果里面包含空格或者特殊字符可以等量替换然后在进行查询 87 select * from TM_Goods where REPLACE(Name,‘ ‘,‘‘)=REPLACE(‘海立得 车载型空气净化机‘,‘ ‘,‘‘) 88  89 ---死锁有关脚本 90 create  proc   [dbo].[p_lockinfo]      91   @kill_lock_spid   bit=1,     --是否杀掉死锁的进程,1   杀掉,   0   仅显示      92   @show_spid_if_nolock   bit=1   --如果没有死锁的进程,是否显示正常进程信息,1   显示,0   不显示      93   as      94   declare   @count   int,@s   nvarchar(1000),@i   int      95   select   id=identity(int,1,1),标志,      96     进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,      97     数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,      98     登陆时间=login_time,打开事务数=open_tran,   进程状态=status,      99     工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,     100     域名=nt_domain,网卡地址=net_address     101   into   #t   from(     102     select   标志=‘死锁的进程‘,     103       spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,     104       status,hostname,program_name,hostprocess,nt_domain,net_address,     105       s1=a.spid,s2=0     106     from   master..sysprocesses   a   join   (     107       select   blocked   from   master..sysprocesses   group   by   blocked     108       )b   on   a.spid=b.blocked   where   a.blocked=0     109     union   all     110     select   ‘|_牺牲品_>‘,     111       spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,     112       status,hostname,program_name,hostprocess,nt_domain,net_address,     113       s1=blocked,s2=1     114     from   master..sysprocesses   a   where   blocked<>0     115   )a   order   by   s1,s2     116       117   select   @count=@@rowcount,@i=1     118       119   if   @count=0   and   @show_spid_if_nolock=1     120   begin     121     insert   #t     122     select   标志=‘正常的进程‘,     123       spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,     124       open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address     125     from   master..sysprocesses     126     set   @count=@@rowcount     127   end     128       129   if   @count>0     130   begin     131     create   table   #t1(id   int   identity(1,1),a   nvarchar(30),b   Int,EventInfo   nvarchar(255))     132     if   @kill_lock_spid=1     133     begin     134       declare   @spid   varchar(10),@标志   varchar(10)     135       while   @i<=@count     136       begin     137         select   @spid=进程ID,@标志=标志   from   #t   where   id=@i     138         insert   #t1   exec(‘dbcc   inputbuffer(‘+@spid+‘)‘)     139         if   @标志=‘死锁的进程‘   exec(‘kill   ‘+@spid)     140         set   @i=@i+1     141       end     142     end     143     else     144       while   @i<=@count     145       begin     146         select   @s=‘dbcc   inputbuffer(‘+cast(进程ID   as   varchar)+‘)‘   from   #t   where   id=@i     147         insert   #t1   exec(@s)     148         set   @i=@i+1     149       end     150     select   a.*,进程的SQL语句=b.EventInfo     151     from   #t   a   join   #t1   b   on   a.id=b.id     152   end153 GO