---为分表创建视图
CREATE PROCEDURE create_monthTable_view
@table varchar(100),--表名
@view varchar(100)--视图名

AS
BEGIN
declare @totalcount int
declare @rownum int
DECLARE @tablename VARCHAR(100)
SET @tablename=@table
DECLARE @sql varchar(3000)

SET @sql = 'CREATE VIEW ['+@view+'] AS SELECT * FROM ['+@table+']'
select @totalcount = count(1) from dbo.sysobjects where name like '%'+@table+'_[0-9]%' and OBJECTPROPERTY(id, N'IsUserTable') = 1

set @rownum = 1

while @rownum <= @totalcount
    begin

       SELECT  @tablename=min(name) from dbo.sysobjects where name like '%'+@table+'_[0-9]%' AND name>@tablename and OBJECTPROPERTY(id, N'IsUserTable') = 1
    	set @sql= @sql + ' UNION ALL SELECT * FROM ['+@tablename+']'
        set @rownum = @rownum+1
    end
EXEC(@sql)
END
GO
-- 使用
IF OBJECT_ID ('dbo.WB_Log_Printer_View') IS NOT NULL
	DROP VIEW dbo.WB_Log_Printer_View
GO
EXEC create_monthTable_view 'WB_Log_Printer','WB_Log_Printer_View'
GO

---为分表添加列
create proc add_column

@table varchar(100),--表名
@columns varchar(100),--字段名
@type varchar(100)--数据类型（如不设置，默认为varchar(100)）

as
declare @sql varchar(3000),@type_code varchar(100)

if @type=''
begin
set @type_code='varchar(100)'
end
else
begin
set @type_code=@type

set @sql='if NOT EXISTS (select a.Name as columnName
from syscolumns as a
, sysobjects as b where a.ID=b.ID
and b.Name='''+@table+'''
and a.name='''+@columns+''')
BEGIN
alter table '+@table+' add '+@columns+' '+@type_code+'

select a.Name as columnName
from syscolumns as a
, sysobjects as b where a.ID=b.ID
and b.Name='''+@table+'''
END
ELSE
BEGIN
PRINT ''字段已经存在！''
END
'
--print @sql
exec(@sql)
end
go

CREATE PROCEDURE alter_monthTable
@table varchar(100),--表名
@columns varchar(100),--字段名
@type varchar(100)--数据类型（如不设置，默认为varchar(100)）

AS
BEGIN
declare @totalcount int
declare @rownum int
DECLARE @tablename VARCHAR(100)
SET @tablename=@table

select @totalcount = count(1) from dbo.sysobjects where name like '%'+@table+'_[0-9]%' and OBJECTPROPERTY(id, N'IsUserTable') = 1

set @rownum = 1

while @rownum <= @totalcount
    begin
       declare @sql varchar(3000)
       SELECT  @tablename=min(name) from dbo.sysobjects where name like '%'+@table+'_[0-9]%' AND name>@tablename and OBJECTPROPERTY(id, N'IsUserTable') = 1
       EXEC add_column @tablename,@columns,@type
        set @rownum = @rownum+1

    end

END
GO
-- 使用
EXEC alter_monthTable 'WB_Log_Printer','[Is_Not_Invalid]', '[bit] NULL'
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_who_lock]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
-- 删除存储过程
drop procedure [dbo].[sp_who_lock]
GO

-- 创建数据库表死锁检查存储过程
CREATE procedure [dbo].[sp_who_lock]
as
begin
   declare @spid int
   declare @blk int
   declare @count int
   declare @index int
   declare @lock tinyint
   set @lock=0
   create table #temp_who_lock
 (
  id int identity(1,1),
  spid int,
  blk int
 )
 if @@error<>0 return @@error
 insert into #temp_who_lock(spid,blk)
 select 0 ,blocked
 from (select * from master..sysprocesses where blocked>0)a
 where not exists(select * from  master..sysprocesses where a.blocked =spid and blocked>0)
 union select spid,blocked from  master..sysprocesses where blocked>0
 if @@error<>0 return @@error
 select @count=count(*),@index=1 from #temp_who_lock
 if @@error<>0 return @@error
 if @count=0
 begin
  select '没有阻塞和死锁信息' as info
  return 0
 end
 while @index<=@count
 begin
  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))
  begin
   set @lock=1
   select @spid=spid,@blk=blk from #temp_who_lock where id=@index
   select '进程号SPID: '+ CAST(@spid AS VARCHAR(10)) + '引起数据库死锁，其执行的SQL语法如下：'  as info
   select  @spid, @blk
   dbcc inputbuffer(@spid)
   dbcc inputbuffer(@blk)
  end
  set @index=@index+1
 end
 if @lock=0
 begin
  set @index=1
  while @index<=@count
  begin
   select @spid=spid,@blk=blk from #temp_who_lock where id=@index
   if @spid=0
    select '进程号SPID：'+ cast(@blk as varchar(10))+ '引发阻塞，其执行的SQL语法如下：' as info
   else
    select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ ' 被' + '进程号SPID：'+ CAST(@blk AS VARCHAR(10)) +'阻塞，其执行的SQL语法如下：' as info
   dbcc inputbuffer(@spid)
   dbcc inputbuffer(@blk)
   set @index=@index+1
  end
 end
 drop table #temp_who_lock
 return 0
end
go