–获取数据库中的表的记录数，并倒排序

create procedure alltablecount as
declare @name varchar(100)
declare tablecur cursor for select name from sysobjects where xtype= 'u '
create table #tablecount(tablename varchar(100),reccount int)
open tablecur
fetch next from tablecur into @name
while @@fetch_status!=-1
begin
exec ('insert into #tablecount select tablename='''+@name+''',reccount=count(1) from '+@name+' ')
fetch next from tablecur into @name
end
close tablecur
deallocate tablecur
select * from #tablecount order by reccount desc
go
exec alltablecount
drop procedure alltablecount

–清空数据表中的数据

DECLARE tables_cursor CURSOR
   FOR
   SELECT name FROM sysobjects WHERE type = 'U' --选择用户表名
OPEN tables_cursor --打开游标连接
DECLARE @tablename varchar(40)  -- 定义变量
FETCH NEXT FROM tables_cursor INTO @tablename   --结果集中一行一行读取表名
WHILE (@@FETCH_STATUS <> -1)  --判断游标状态
BEGIN
print @tablename
   if(@tablename like 'y_%') EXEC ('TRUNCATE TABLE ' + @tablename)   --清空表中的数据
   FETCH NEXT FROM tables_cursor INTO @tablename  --下一行数据
END
close tables_cursor
DEALLOCATE tables_cursor  --关闭游标
go

–效率最高的翻页存储过程

ALTER  PROCEDURE [dbo].[GetRecordFromPage]
(
@tblName varchar(500),--表名
@PageSize int=20,--页尺寸
@PageIndex int=1,--页码
@IsCount bit=0,--返回记录总数,非0值则返回
@strOrderSyntax varchar(800),---排序的具体语法
@strWhere varchar(5000)='',--查询条件(注意:不要加where)
@fldName varchar(800),
@Onlyzd varchar(50)='',--唯一字段
@Total int output
)
AS
Begin
declare @strSQL nvarchar(2000)--主语句
declare @strTmp varchar(110)--临时变量
declare @strOrder varchar(400)--排序类型
if (CHARINDEX(',',@strOrderSyntax,1)>0)
begin
if @strWhere!=''
set @strSQL=N'select top'+str(@PageSize)+' '+@fldName+ ' from '
+@tblName+' where '+@Onlyzd+'not in'+'(select top'+str((@PageIndex-1)*@PageSize)+' '+@Onlyzd+' from '
+@tblName+' where '+@strWhere+' '+@strOrderSyntax+') and '+@strWhere+ ' ' +@strOrderSyntax
else
set @strSQL=N'select top '+str(@PageSize)+' '+@fldName+' from '
+@tblName+' where '+@Onlyzd+' not in '+'(select top'+str((@PageIndex-1)*@PageSize)+' '+@Onlyzd+' from '
+@tblName+' '+@strOrderSyntax+')'+@strOrderSyntax;
exec sp_executesql @strSQL;
end
else
begin
declare @ordername varchar(100)
if(charindex('desc',@strOrderSyntax,1)>0)
set @strTmp='<(select min'
else
set @strTmp='>(select max'
set @ordername=replace(@strOrderSyntax,'order by','');
set @ordername=replace(@ordername,' desc','');
set @ordername=replace(@ordername,' asc','');
set @ordername=replace(@ordername,' ','');
set @strOrder=@strOrderSyntax
if @PageIndex=1
begin
if @strWhere!=''
set @strSQL='select top '+str(@PageSize)+' '+@fldName+' from '+@tblName+'  where '+@strWhere+' '+@strOrder
else
set @strSQL='select top '+str(@PageSize)+' '+@fldName+' from  '+@tblName+' '+@strOrder
end
else
begin--以下代码赋予了@strSQL以真正执行的SQL代码
set @strSQL='select top '+str(@PageSize)+' '+@fldName+' from '+@tblName+'  where '+@ordername+' '+@strTmp+' ([tt]) from(select top '+str((@PageIndex-1)*@PageSize)+' '+@ordername+' as tt from '+@tblName+' '+@strOrder+') as tblTmp)'+@strOrder
if @strWhere!=''
set @strSQL='select top '+str(@PageSize)+' '+@fldName+' from '+@tblName+'  where '+@ordername+' '+@strTmp+'([tt]) from (select top '+str((@PageIndex-1)*@PageSize)+' '+@ordername+' as tt from  '+@tblName+' where '+@strWhere+''+@strOrder+') as tblTmp ) and '+@strWhere+' '+@strOrder
end
end
print @strSQL
exec(@strSQL)
--用于计算当前记录数(在分页用到)
if	@strWhere!=''
set @strSQL=N'select @Total=count('+@Onlyzd+') from '+@tblName+' Where ('+@strWhere+')'
else
set @strSQL=N'select @Total=count('+@Onlyzd+') from '+@tblName
exec sp_executesql @strSQL,N'@Total int output',@Total output;
if @Total is Null
set @Total=0;
end

–以下是上面存储过程的原型是：

select top 页大小 *
from table1
where id >
(select max (id) from
(select top ((页码-1)*页大小) id from table1 order by id) as T
)
order by id

--查询重复的记录
select * from onetable where guid in
 (select guid from onetable group by guid having(count(guid))>1)

--查询不重复的记录
select * from onetable where guid not in
 (select guid from onetable group by guid having(count(guid))>1)

Tags: mssql, 清空数据表, 翻页存储过程, 获取表的记录数