--8-1 USE Northwind SELECT * FROM ::fn_dblog('', '') GO --8-2 USE Northwind SELECT * FROM ::fn_dblog('', '') WHERE [Begin Time] >= '02/01/07' GO --9-1 SELECT * FROM master.dbo.sysprocesses --9-2 SELECT * FROM sys.dm_exec_requests --9-3 DECLARE @Handle varbinary(64); SELECT @handle = sql_handle FROM master.dbo.sysprocesses WHERE spid = @@SPID SELECT * FROM ::fn_get_sql(@Handle); GO --10-1 方法 USE Northwind GO CREATE FUNCTION dbo.CountOFProductsByCategory(@pCategoryID INT) RETURNS INT AS BEGIN RETURN ( SELECT COUNT(*) FROM dbo.Products WHERE CategoryID = @pCategoryID ) END GO --10-2 SELECT COUNT(*) FROM Products WHERE CategoryID ='1' GO PRINT '属于产品类型的产品有'+CONVERT(VARCHAR(3),dbo.CountOFProductsByCategory(1))+'种。' GO --10-3 USE Northwind GO PRINT '属于产品类型的产品有' PRINT '属于产品类型的产品有'+CONVERT(VARCHAR(3),dbo.CountOFProductsByCategory(DEFAULT))+'种。' GO --11-1 方法返回 TABLE USE Northwind GO CREATE FUNCTION dbo.ProductsByCategory(@pCategoryID INT) RETURNS TABLE AS RETURN SELECT ProductID,CategoryID,ProductName,UnitPrice FROM dbo.Products WHERE CategoryID = @pCategoryID GO --11-2 USE Northwind GO Select * FROM DBO.ProductsByCategory('1') --12-1 方法返回 TABLE2 USE Northwind GO CREATE 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,@CountsOfRec) SET @lminCID =@lminCID+1 SELECT @CountsOfRec = Count(*) FROM Products WHERE CategoryID = @lminCID END RETURN END --12-2 SELECT * FROM dbo.CategoryInfo() --12-3 SELECT * FROM dbo.CategoryInfo() WHERE CategoryID = 6 --17-1 select * from sys.assembly_modules If exists(select * from sys.assembly_modules where assembly_class = 'ClrAggregate.AvgUnitPriceOfOrderDetails') drop AGGREGATE AvgUnitPriceOfOrderDetails GO select * from sys.assemblies --17-2 IF EXISTS (select * from sys.assemblies where name = N'ClrAggregate') DROP ASSEMBLY ClrAggregate; GO