27,579
社区成员
发帖
与我相关
我的任务
分享
create function f_test
(@tb varchar(10))
returns int
as
begin
declare @i int
select @i=rowcnt from dbo.sysindexes where id = object_id(@tb,'U') and indid=0
return @i
end
1)既然从sysindexes读取Rowcount,那么where id = object_id(@tb,'U')是多余的条件.
不过可以过滤传的参数是否是表.
2)and indid=0是堆的情况,如果有聚集索引呢?!
3)而简单的如下代码,也不对.得到的结果可能为0.因为sysindexes存的不仅仅是Index的信息,还有Statistics的信息.
begin
declare @i int
select @i=rowcnt
from dbo.sysindexes
where id = object_id(@tb,'U')
return @i
end
--4)改进后的.
CREATE FUNCTION ufn_TabCount
(@tb varchar(300))
RETURNS INT
AS
BEGIN
IF object_id(@tb,'U') IS NULL
BEGIN
RETURN -999999
END
DECLARE @i int
SELECT @i=rowcnt FROM sys.sysindexes ind
WHERE OBJECT_NAME(id) = @tb
AND OBJECTPROPERTY(id,'IsMSShipped') = 0
AND(indid = 1 OR indid = 0)
RETURN @i
END
5) 测试
PRINT dbo.ufn_TabCount('_tabDetails')
359
PRINT dbo.ufn_TabCount('tbl_Show')
-999999
IF OBJECT_ID('[p_test]') IS NOT NULL
DROP PROC [p_test]
GO
CREATE PROC [p_test]
@tbname SYSNAME,
@cnt INT OUT
AS
DECLARE @sql NVARCHAR(4000)
SET @sql = N'SELECT @cnt = COUNT(1) FROM ['+@tbname+']'
EXEC sp_executesql @sql, N'@cnt INT OUT', @cnt OUT
GO
DECLARE @cnt INT
EXEC [p_test] tb,@cnt OUT
PRINT @cnt
/*
31
*/
存储过程的方式--函数
create function fn_name
(@tb varchar(10))
returns int
as
begin
declare @i int
select @i=rowcnt
from dbo.sysindexes
where id = object_id(@tb,'U')
return @i
end
exec sp_executesql @sql,N'@count int out',@count int out
declare @count int
declare @sql nvarchar(1000)
select @sql='select @count=count (1) from'+ @tableName
exec sp_executesql @sql,N'@count int output',@count output
select @count
declare @sql nvarchar(1000)
declare @count int
set @sql='select @count=count(1) from '+@tableName
exec sp_executesql @sql,N'@count int out',@count int
select @count