H2zGBYLa

1---为分表创建视图
2CREATE PROCEDURE create_monthTable_view
3@table varchar(100),--表名
4@view varchar(100)--视图名
5
6AS
7BEGIN
8declare @totalcount int
9declare @rownum int
10DECLARE @tablename VARCHAR(100)
11SET @tablename=@table
12DECLARE @sql varchar(3000)
13
14SET @sql = 'CREATE VIEW ['+@view+'] AS SELECT * FROM ['+@table+']'
15select @totalcount = count(1) from dbo.sysobjects where name like '%'+@table+'_[0-9]%' and OBJECTPROPERTY(id, N'IsUserTable') = 1
16
17set @rownum = 1
18
19while @rownum <= @totalcount
20    begin
21       
22       SELECT  @tablename=min(name) from dbo.sysobjects where name like '%'+@table+'_[0-9]%' AND name>@tablename and OBJECTPROPERTY(id, N'IsUserTable') = 1
23    	set @sql= @sql + ' UNION ALL SELECT * FROM ['+@tablename+']'
24        set @rownum = @rownum+1
25    end
26EXEC(@sql)
27END
28GO
29-- 使用
30IF OBJECT_ID ('dbo.WB_Log_Printer_View') IS NOT NULL
31	DROP VIEW dbo.WB_Log_Printer_View
32GO
33EXEC create_monthTable_view 'WB_Log_Printer','WB_Log_Printer_View'
34GO
35
36---为分表添加列
37create proc add_column
38
39@table varchar(100),--表名
40@columns varchar(100),--字段名
41@type varchar(100)--数据类型（如不设置，默认为varchar(100)）
42
43as
44declare @sql varchar(3000),@type_code varchar(100)
45
46if @type=''
47begin
48set @type_code='varchar(100)'
49end
50else
51begin
52set @type_code=@type
53
54set @sql='if NOT EXISTS (select a.Name as columnName
55from syscolumns as a
56, sysobjects as b where a.ID=b.ID
57and b.Name='''+@table+'''
58and a.name='''+@columns+''')
59BEGIN
60alter table '+@table+' add '+@columns+' '+@type_code+'
61
62select a.Name as columnName
63from syscolumns as a
64, sysobjects as b where a.ID=b.ID
65and b.Name='''+@table+'''
66END
67ELSE
68BEGIN
69PRINT ''字段已经存在！''
70END
71'
72--print @sql
73exec(@sql)
74end 
75go
76
77CREATE PROCEDURE alter_monthTable
78@table varchar(100),--表名
79@columns varchar(100),--字段名
80@type varchar(100)--数据类型（如不设置，默认为varchar(100)）
81
82AS
83BEGIN
84declare @totalcount int
85declare @rownum int
86DECLARE @tablename VARCHAR(100)
87SET @tablename=@table
88
89select @totalcount = count(1) from dbo.sysobjects where name like '%'+@table+'_[0-9]%' and OBJECTPROPERTY(id, N'IsUserTable') = 1
90
91set @rownum = 1
92
93while @rownum <= @totalcount
94    begin
95       declare @sql varchar(3000)
96       SELECT  @tablename=min(name) from dbo.sysobjects where name like '%'+@table+'_[0-9]%' AND name>@tablename and OBJECTPROPERTY(id, N'IsUserTable') = 1
97       EXEC add_column @tablename,@columns,@type
98        set @rownum = @rownum+1
99         
100    end
101
102END
103GO
104-- 使用
105EXEC alter_monthTable 'WB_Log_Printer','[Is_Not_Invalid]', '[bit] NULL'
106GO
107
108if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_who_lock]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
109-- 删除存储过程
110drop procedure [dbo].[sp_who_lock]
111GO
112
113-- 创建数据库表死锁检查存储过程
114CREATE procedure [dbo].[sp_who_lock]  
115as     
116begin     
117   declare @spid int     
118   declare @blk int     
119   declare @count int     
120   declare @index int     
121   declare @lock tinyint      
122   set @lock=0      
123   create table #temp_who_lock      
124 (      
125  id int identity(1,1),      
126  spid int,      
127  blk int     
128 )      
129 if @@error<>0 return @@error      
130 insert into #temp_who_lock(spid,blk)      
131 select 0 ,blocked       
132 from (select * from master..sysprocesses where blocked>0)a      
133 where not exists(select * from  master..sysprocesses where a.blocked =spid and blocked>0)      
134 union select spid,blocked from  master..sysprocesses where blocked>0      
135 if @@error<>0 return @@error      
136 select @count=count(*),@index=1 from #temp_who_lock      
137 if @@error<>0 return @@error      
138 if @count=0      
139 begin     
140  select '没有阻塞和死锁信息' as info    
141  return 0      
142 end     
143 while @index<=@count      
144 begin     
145  if exists(select 1 from #temp_who_lock a where id>@index and exists(select 1 from #temp_who_lock where id<=@index and a.blk=spid))      
146  begin     
147   set @lock=1      
148   select @spid=spid,@blk=blk from #temp_who_lock where id=@index     
149   select '进程号SPID: '+ CAST(@spid AS VARCHAR(10)) + '引起数据库死锁，其执行的SQL语法如下：'  as info   
150   select  @spid, @blk    
151   dbcc inputbuffer(@spid)      
152   dbcc inputbuffer(@blk)      
153  end     
154  set @index=@index+1      
155 end     
156 if @lock=0       
157 begin     
158  set @index=1      
159  while @index<=@count      
160  begin     
161   select @spid=spid,@blk=blk from #temp_who_lock where id=@index     
162   if @spid=0      
163    select '进程号SPID：'+ cast(@blk as varchar(10))+ '引发阻塞，其执行的SQL语法如下：' as info    
164   else      
165    select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ ' 被' + '进程号SPID：'+ CAST(@blk AS VARCHAR(10)) +'阻塞，其执行的SQL语法如下：' as info    
166   dbcc inputbuffer(@spid)    
167   dbcc inputbuffer(@blk)      
168   set @index=@index+1      
169  end     
170 end     
171 drop table #temp_who_lock      
172 return 0      
173end      
174go