sql 动态管理函数,自定义函数

--动态管理函数
select login_name,count(session_id) as session_count from
sys.dm_exec_sessions group by login_name

select * from sys.dm_exec_sessions
select * from sys.dm_exec_requests--了解当前数据库进程的阻塞信息

select * from ::fn_dblog('','')--sql server 的事务日志以数据表记录的形式返回给用户,可监视数据表更新、插入和删除等事件操作
select * from ::fn_dblog('','') where [Begin Time] >= '01/01/07'

select * from master.dbo.sysprocesses

declare @Handle Varbinary(64);
select @handle = sql_handle from master.dbo.sysprocesses where spid=@@SPID--@@SPID为当前执行的
select * from ::fn_get_sql(@Handle);-- ::fn_get_sql可轻松获取被一个SQL进程执行的sql文本，如要诊断或调试出现的某种死锁或阻塞问题

 

 

 

 

---值类型自定义函数
create function dbo.CountOFProductsByCategory(@pCategoryID int)
alter function dbo.CountOFProductsByCategory(@pCategoryID int=1)--默认值
returns int
as
begin
 return(select count(*) from dbo.Products where CategoryID = @pCategoryID)
end

print '产品有'+Convert(varchar(3),dbo.CountOFProductsByCategory(1))+'种'
print '产品有'+Convert(varchar(3),dbo.CountOFProductsByCategory(default))+'种'--默认值调用

------返回表自定义函数
alter function dbo.ProductsByCategory(@pCategoryID int)
returns table
as
 return
  select ProductID,CategoryID,ProductName,UnitPrice from Products
  where CategoryID = @pCategoryID

go

 select * from ProductsByCategory('1')

-------多语句自定义函数
alter function dbo.CategoryInfo()
returns @CategoryInfo table
(
 CategoryID int,
 NumberOfProducts int
)
as
 begin
  declare @lminCID int
  declare @lmaxCID int
     declare @CountsOfRec int

  select @lminCID = Min(CategoryID) from products
     select @lmaxCID = Max(CategoryID) from products

  select @CountsOfRec = count(*) from products where CategoryID = @lminCID
  while @lminCID <= @lmaxCID
  begin
   insert into @CategoryInfo values(@lminCID,@lmaxCID)
   set @lminCID = @lminCID+1
   select @CountsOfRec = count(*) from products where CategoryID = @lminCID
  end
  return
 end
go

select * from dbo.CategoryInfo()

 

select * from dbo.CategoryInfo() where CategoryID = 6

 

 

 

 

 

 

 

 

 

Microsoft SQL Server 自定义函数整理大全

http://blog.csdn.net/maco_wang/archive/2011/03/19/6261639.aspx