高效分页方法代码(sql百万级数据量分页代码)

高效分页方法代码(sql百万级数据量分页代码)

综合网络资料整理]

–得到数据库中所有用户表
Select [name] from sysObjects Where xtype=’U’and
[name]<>’dtproperties’ Order By [name]
–得到数据库中所有用户视图
Select [name] From sysObjects Where xtype=’V’ And
[name]<>’syssegments’ And [name]<>’sysconstraints’ Order
By [name]
–获得指定表中所有的列
Select
c.name As ColumnName,
t.name As TypeName
From syscolumns c, systypes t, sysobjects o
Where c.xtype = t.xusertype
And c.id = o.id
And o.name = ‘Book’
Order By c.colorder
–获得表中所有列的详细信息
Select  ColOrder = col.colorder, –排序号
ColumnName = col.name, –列名
TypeName = type.name,–数据类型名称
Length =  (Case When type.name=’nvarchar’ Or type.name=’nchar’ Then
col.length/2 Else col.length End), –长度
[PRECISION] = COLUMNPROPERTY(col.id, col.name, ‘PRECISION’), –精度
Scale = ISNULL(COLUMNPROPERTY(col.id, col.name, ‘Scale’), 0), –小数
IsIdentity = Case When COLUMNPROPERTY(col.id, col.name, ‘IsIdentity’)=1
Then ‘√’ Else ” End, –是否为自动编号列
IsPK = Case When Exists(Select 1 From sysobjects Where xtype = ‘PK’ And
name In (
     Select name From sysindexes Where indid In (
      Select indid From sysindexkeys Where id = col.id And colid =
col.colid
      )
     )
    ) Then ‘√’ Else ” End, –是否为主键
AllowNull = Case When col.isnullable=1 Then ‘√’ Else ” End,
–是否允许为空
DefalutValue = isnull(com.text, ”) –默认值
From syscolumns col
Left Join systypes type On col.xtype = type.xusertype
Inner Join sysobjects obj On col.id = obj.id And (obj.xtype = ‘U’ Or
obj.xtype = ‘V’) And obj.name <> ‘dtproperties’
Left Join syscomments com On col.cdefault = com.id
Where obj.name = ‘Territories’

@querystr nvarchar(300),–表名、视图名、查询语句@pagesize
int=10,–每页的大小(行数)@pagecurrent int=1,–要显示的页@fdshow nvarchar
(100)=”,–要显示的字段列表,如果查询结果有标识字段,需要指

1.获取所有数据库名:
   (1)、Select Name FROM
Master..SysDatabases order by Name
2.**获取所有表名:

=============================================
获取MS SQL库数据字典的经典SQL语句

定此值,且不包含标识字段@fdorder nvarchar
(100)=”,–排序字段列表@wherestr nvarchar (200)=”, –内容是’ id=3 and
model_no like ‘%24%’

**   (1)、Select Name FROM SysObjects Where
XType=’U’ orDER BY Name
           XType=’U’:表示所有用户表;
           XType=’S’:表示所有系统表;

SELECT sysobjects.name AS [table], sysproperties.[value] AS
表说明,
syscolumns.name AS field, properties.[value] AS 字段说明,
systypes.name AS type,
syscolumns.length, ISNULL(COLUMNPROPERTY(syscolumns.id,
syscolumns.name,
‘Scale’), 0) AS 小数位数, syscolumns.isnullable AS isnull,
CASE WHEN syscomments.text IS NULL
THEN ” ELSE syscomments.text END AS [Default],
CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, ‘IsIdentity’)
= 1 THEN ‘√’ ELSE ” END AS 标识, CASE WHEN EXISTS
(SELECT 1
FROM sysobjects
WHERE xtype = ‘PK’ AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = syscolumns.id AND colid = syscolumns.colid)))
THEN ‘√’ ELSE ” END AS 主键
FROM syscolumns INNER JOIN
sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
sysproperties properties ON syscolumns.id = properties.id AND
syscolumns.colid = properties.smallid LEFT OUTER JOIN
sysproperties ON sysobjects.id = sysproperties.id AND
sysproperties.smallid = 0 LEFT OUTER JOIN
syscomments ON syscolumns.cdefault = syscomments.id

and ‘@rscount int=0 output asset @fdshow=’ ‘+@fdshow+’ ‘set @fdorder= ‘
‘+@fdorder+’ ‘set @wherestr= ‘ ‘+@wherestr+’ ‘

   (2)、SELECT name FROM sysobjects WHERE type = ‘U’ AND sysstat =
’83’

WHERE (sysobjects.xtype = ‘U’)

获取数据库中表的字段的名称及类型

select   syscolumns.name,systypes.name       from      
syscolumns,systypes     where       id=object_id( ‘POSmanage..PayWays
‘)   and   systypes.xusertype=syscolumns.xusertype
给你一个通过查询系统表得到纵向的表结构的例子.完全可以满足你的要求.
SELECT 
表名=case   when   a.colorder=1   then   d.name   else   ‘ ‘   end,
表说明=case   when   a.colorder=1   then   isnull(f.value, ‘ ‘)   else  
‘ ‘   end,
字段序号=a.colorder,
字段名=a.name,
标识=case   when   COLUMNPROPERTY(   a.id,a.name, ‘IsIdentity ‘)=1  
then   ‘√ ‘else   ‘ ‘   end,
主键=case   when   exists(SELECT   1   FROM   sysobjects   where  
xtype= ‘PK ‘   and   name   in   (
SELECT   name   FROM   sysindexes   WHERE   indid   in(
SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND  
colid=a.colid
)))   then   ‘√ ‘   else   ‘ ‘   end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name, ‘PRECISION ‘),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name, ‘Scale ‘),0),
允许空=case   when   a.isnullable=1   then   ‘√ ‘else   ‘ ‘   end,
默认值=isnull(e.text, ‘ ‘),
字段说明=isnull(g.[value], ‘ ‘)
FROM   syscolumns   a
left   join   systypes   b   on   a.xusertype=b.xusertype
inner   join   sysobjects   d   on   a.id=d.id   and   d.xtype= ‘U ‘  
and   d.name <> ‘dtproperties ‘
left   join   syscomments   e   on   a.cdefault=e.id
left   join   sysproperties   g   on   a.id=g.id   and  
a.colid=g.smallid
left   join   sysproperties   f   on   d.id=f.id   and   f.smallid=0
–where   d.name= ‘shebei ‘

declare @fdname nvarchar(250)–表中的主键或表、临时表中的标识列名,@id1
varchar(20),@id2 varchar(20)–开始和结束的记录号,@obj_id int
–对象id,@temp nvarchar(300) –临时语句,@strparam nvarchar(100)
–临时参数

           注意:一般情况只需要type =
‘U’,但有时候会有系统表混在其中(不知道什么原因),加上后面一句后就能删除这些系统表了

order   by   a.id,a.colorder

用SQL查询分析器查询表的字段类型长度和表说明

SELECT sysobjects.name AS 表名, syscolumns.name AS 列名,
systypes.name AS 数据类型, syscolumns.length AS 数据长度,
CONVERT(char,
sysproperties.[value]) AS 注释
FROM sysproperties RIGHT OUTER JOIN
sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype ON
sysproperties.id = syscolumns.id AND
sysproperties.smallid = syscolumns.colid
WHERE (sysobjects.xtype = ‘u’ OR
sysobjects.xtype = ‘v’) AND (systypes.name <> ‘sysname’)
–and CONVERT(char,sysproperties.[value]) <> ‘null’
–导出注释不为’null’的记录
–AND (sysobjects.name = ‘bbs_bank_log’)
–逐个关联表名,可以用or连接条件

declare @strfd nvarchar(2000)–复合主键列表,@strjoin
nvarchar(4000)–连接字段,@strwhere
nvarchar(2000)–查询条件–检查输入参数set
@querystr=ltrim(rtrim(@querystr))select
@obj_id=object_id(@querystr),@fdshow=case isnull(@fdshow,”) when ”
then ‘ *’ else ‘ ‘+@fdshow end,@fdorder=case isnull(@fdorder,”) when
” then ” else ‘ order by

3.**获取所有字段名:**
(1)、Select Name FROM SysColumns Where
id=Object_Id(‘TableName’)

ORDER BY 表名

查询表字段、注释和类型

–查询表字段、注释、类型
select B.name,C.value,D.name from sysobjects AS A inner join syscolumns
as B
on A.id=B.id inner join sysproperties as c on B.id=C.id and
B.colid=C.smallid inner join systypes D on B.xtype = D.xtype

‘+@fdorder end,@querystr=case when @obj_id is not null then ‘
‘+@querystr else ‘

(2)、SELECT syscolumns.name,systypes.name,syscolumns.isnullable,syscolumns.length
FROM syscolumns, systypes WHERE syscolumns.xusertype =
systypes.xusertype AND “syscolumns.id =
object_id(‘tableName’)

where a.name=’branchinfo’

SQL单引号的转义

create proc TestPro
(@conditon varchar(50))
as
declare @sql varchar(1000)
set @sql=’select * from test’
if @conditon!=”
set @sql=@sql+’ where name=”’+@conditon+””–”转义成’
exec(@sql)
go
–调用存储过程

(‘+@querystr+’) a’ end–输出总记录数set @temp= ‘select
@rscount=count(*) from ‘ + @querystr+’ ‘+@wherestrset @strparam =
n’@rscount int out’execute sp_executesql @temp,@strparam,@rscount
out–如果显示第一页,可以直接用top来完成if @pagecurrent=1beginselect
@id1=cast(@pagesize as varchar(20))exec(‘select top ‘+@id1+@fdshow+’
from
‘+@querystr+@wherestr+@fdorder)returnend–如果是表,则检查表中是否有标识更或主键if
@obj_id is not null and objectproperty(@obj_id,’istable’)=1beginselect
@id1=cast(@pagesize as
varchar(20)),@id2=cast((@pagecurrent-1)*@pagesize as varchar(20))select
@fdname=name from syscolumns where id=@obj_id and status=0x80if
@@rowcount=0–如果表中无标识列,则检查表中是否有主键beginif not
exists(select 1 from sysobjects where parent_obj=@obj_id and

       注意点:
     (a)这里为了重点突出某些重要内容,选取了其中几项信息输出。
     (b)syscolumns表中只含有数据类型编号,要获取完整的名字需要从systypes表中找,一般用户使用的数据类型用xusertype对应比较好,不会出现一对多的情况。
     (c)syscolumns.length得到的是物理内存的长度,所以nvarchar和varchar等类型在数据库中的显示是这个的一半。

exec TestPro ‘b’

根据syscolumns得到id所对应的表

查看字段所属表
select * from dbo.sysobjects where id in

xtype=’pk’)goto lbusetemp–如果表中无主键,则用临时表处理select
@fdname=name from syscolumns where id=@obj_id and colid in(select colid
from sysindexkeys where @obj_id=id and indid in(select indid from
sysindexes where @obj_www.9778.com,id=id and name in(select name from sysobjects
where xtype=’pk’ and parent_obj=@obj_id)))

 

(select id from dbo.syscolumns where name=’列名’)

一条语句查询数据库中所有表的信息

SELECT 表名=case when a.colorder=1 then d.name else ‘ end,
表说明=case when a.colorder=1 then isnull(f.value,’) else ‘ end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY( a.id,a.name,’IsIdentity’)=1 then ‘√’else
‘ end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype=’PK’ and name
in (SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM
sysindexkeys WHERE id = a.id AND colid=a.colid))) then ‘√’ else ‘ end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,’PRECISION’),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,’Scale’),0),
允许空=case when a.isnullable=1 then ‘√’else ‘ end,
默认值=isnull(e.text,’),
字段说明=isnull(g.[value],’)
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype=’U’ and
d.name<>’dtproperties’
left join syscomments e on a.cdefault=e.id
left join sysproperties g on a.id=g.id and a.colid=g.smallid
left join sysproperties f on d.id=f.id and f.smallid=0
–where d.name=’此处可添加要查询的表名’
Order by a.id,a.colorder
可以在where语句中填写要查询指定表的名称,将显示指定的结构信息和说明。
sqlserver系统表结构说明

该说明在帮助文档中应该也是能查询到的
sysaltfiles  主数据库 保存数据库的文件 syscharsets 
主数据库字符集与排序顺序
sysconfigures 主数据库 配置选项
syscurconfigs 主数据库当前配置选项
  sysdatabases 主数据库服务器中的数据库
  syslanguages 主数据库语言
  syslogins 主数据库 登陆帐号信息
sysoledbusers 主数据库 链接服务器登陆信息
  sysprocesses 主数据库进程
  sysremotelogins主数据库 远程登录帐号
  syscolumns 每个数据库 列
  sysconstrains 每个数据库 限制
  sysfilegroups 每个数据库 文件组
  sysfiles 每个数据库 文件
  sysforeignkeys 每个数据库 外部关键字
  sysindexs 每个数据库 索引
  sysmenbers 每个数据库角色成员
  sysobjects 每个数据库所有数据库对象
  syspermissions 每个数据库 权限
  systypes 每个数据库 用户定义数据类型
  sysusers 每个数据库 用户

if @@rowcount1–检查表中的主键是否为复合主键beginselect
@strfd=”,@strjoin=”,@strwhere=”select
@strfd=@strfd+’,[‘+name+’]’,@strjoin=@strjoin+’ and
a.[‘+name+’]=b.[‘+name+’]’,@strwhere=@strwhere+’ and b.[‘+name+’]
is null’from syscolumns where id=@obj_id and colid in(select colid from
sysindexkeys where @obj_id=id and indid in(select indid from sysindexes
where @obj_id=id and name in(select name from sysobjects where
xtype=’pk’ and parent_obj=@obj_id)))select
@strfd=substring(@strfd,2,2000),@strjoin=substring(@strjoin,5,4000),@strwhere=substring(@strwhere,5,4000)goto
lbusepkendendendelsegoto
lbusetemp/*–使用标识列或主键为单一字段的处理方法–*/lbuseidentity: if
len(@wherestr)10beginexec(‘select top ‘+@id1+@fdshow+’ from
‘+@querystr+@wherestr+’ and ‘+@fdname+’ not in(select top ‘+@id2+’
‘+@fdname+’ from
‘+@querystr+@wherestr+@fdorder+’)’+@fdorder)returnendelsebeginexec(‘select
top ‘+@id1+@fdshow+’ from ‘+@querystr+’ where ‘+@fdname+’ not in(select
top ‘+@id2+’ ‘+@fdname+’ from
‘+@querystr+@fdorder+’)’+@fdorder)returnend/*–表中有复合主键的处理方法–*/lbusepk:exec(‘select
‘+@fdshow+’ from(select top ‘+@id1+’ a.* from(select top 100 percent *
from ‘+@querystr+@fdorder+’) aleft join (select top ‘+@id2+’ ‘+@strfd+’
from ‘+@querystr+@fdorder+’) b on ‘+@strjoin+’where ‘+@strwhere+’)
a’)return/*–用临时表处理的方法–*/lbusetemp:select
@fdname='[id_’+cast(newid() as
varchar(40))+’]’,@id1=cast(@pagesize*(@pagecurrent-1) as
varchar(20)),@id2=cast(@pagesize*@pagecurrent-1 as
varchar(20))exec(‘select ‘+@fdname+’=identity(int,0,1),’+@fdshow+’into
#tb from’+@querystr+@fdorder+’select ‘+@fdshow+’ from #tb where
‘+@fdname+’ between ‘+@id1+’ and ‘+@id2)

4、得到表中主键所包含的列名:

    SELECT syscolumns.name FROM
syscolumns,sysobjects,sysindexes,sysindexkeys WHERE syscolumns.id =
object_id(‘tablename’) AND sysobjects.xtype = ‘PK’ AND
sysobjects.parent_obj = syscolumns.id AND sysindexes.id = syscolumns.id
AND sysobjects.name = sysindexes.name AND sysindexkeys.id =
syscolumns.id AND sysindexkeys.indid = sysindexes.indid AND
syscolumns.colid = sysindexkeys.colid

注意:这是在4张系统表中寻找的,关系比较复杂,大致可以表示为:
syscolumns中存有表中的列信息和表id,sysobjects表中存有主键名字(即PK_Table类似)和表id,sysindexes中存
有主键名字和表id和index编号,sysindexkeys中存有表id和index编号和列编号,一项一项对应起来后就能找到列名了。

 

另外的SQL代码

select syscolumns.name,
systypes.name,
syscolumns.length from syscolumns   

  left join
systypes on syscolumns. xusertype
=systypes. xusertype
 

  where id=(select
id from sysobjects where name=’订货主档’);

go;

或者用这样的写法,执行结果一样:

select syscolumns.name,
systypes.name,
syscolumns.length from syscolumns,systypes

where (syscolumns.id=object_id(‘订货主档’) and syscolumns.xusertype=systypes.xusertype)

order
by syscolumns.colorder;

go

执行结果:(字段只出现一次,正常)

订单号码   
int4

客户编号   
nvarchar    10

员工编号   
int4

订单日期   
datetime    8

要货日期   
datetime    8

送货日期   
datetime    8

送货方式   
int4

运费    money  
8

收货人  nvarchar   
80

送货地址   
nvarchar    120

送货城市   
nvarchar    30

送货行政区 
nvarchar    30

送货邮政编码   
nvarchar    20

送货国家地区   
nvarchar    30

 

 

select syscolumns.name,
systypes.name,
syscolumns.length from syscolumns   

  left join
systypes on syscolumns.xtype=systypes.xtype 

  where id=(select
id from sysobjects where name=’订货主档’);

go;

执行结果:(部分字段出现两次,数据类型不同)

订单号码   
int4

客户编号   
nvarchar   10

客户编号   
sysname    10

员工编号   
int4

订单日期   
datetime   8

订单日期   
出生日期类型   
8

要货日期   
datetime   8

要货日期   
出生日期类型   
8

送货日期   
datetime   8

送货日期   
出生日期类型   
8

送货方式   
int4

运费   
money  8

运费   
薪水类型   
8

收货人 
nvarchar   80

收货人 
sysname    80

送货地址   
nvarchar   120

送货地址   
sysname    120

送货城市   
nvarchar   30

送货城市   
sysname    30

送货行政区 
nvarchar   30

送货行政区 
sysname    30

送货邮政编码   
nvarchar   20

送货邮政编码   
sysname    20

送货国家地区   
nvarchar   30

送货国家地区   
sysname    30

查询存储过程DepartmentSalaryInfo所有的信息,信息包含在系统视图syscolumns,systypes中

select syscolumns.*,
systypes.* from syscolumns   

  left join
systypes on syscolumns.xusertype=systypes.xusertype 

  where id=(select
id from sysobjects where name=’DepartmentSalaryInfo’);

go

 

 

exec   sp_procedure_params_rowset  
@procedure_name   =   ‘DepartmentSalaryInfo’;

go

执行结果:

北风贸易   
dboDepartmentSalaryInfo;1   @RETURN_VALUE0   4   0   NULL   0   3  
NULL   NULL   10    NULL   NULL   intint

北风贸易   
dboDepartmentSalaryInfo;1   @department   1   1   0   NULL   1   12910 
10  NULL    NULL   NULL   varchar    varchar

北风贸易   
dboDepartmentSalaryInfo;1   @average   2   2   0   NULL   1   6   NULL  
NULL   19    NULL   NULL   money  money

北风贸易   
dboDepartmentSalaryInfo;1   @maximum   3   2   0   NULL   1   6   NULL  
NULL   19    NULL   NULL   money  money

北风贸易   
dboDepartmentSalaryInfo;1   @minimum   4   2   0   NULL   1   6   NULL  
NULL   19    NULL   NULL   money  money

 

 

–存储过程中的参数名,参数类型,参数长度

select
syscolumns.name, systypes.name, syscolumns.length from syscolumns   

  left join systypes
on syscolumns.xusertype=systypes.xusertype 

  where id=(select id
from sysobjects where name=’DepartmentSalaryInfo’);

1:获取当前数据库中的所有用户表
select Name from sysobjects where xtype=’u’ and status>=0
2:获取某一个表的所有字段
select name from syscolumns where id=object_id(‘表名’)
3:查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from
master..syslogins where name=’sa’)
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
4:查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = ‘表名’
[n].[标题]:
Select * From TableName Order By CustomerName
[n].[标题]:
8.如何修改数据库的名称:
sp_renamedb ‘old_name’, ‘new_name’
9.只复制一个表结构,不复制数据
select top 0 * into [t1] from [t2]
10.连接远程数据库
select * from OPENDATASOURCE(‘SQLOLEDB’,’Data Source=远程ip;User
ID=sa;Password=密码’).库名.dbo.表名
11.获取当前oracle数据库中的所有表
select table_name from user_tables
12 .获取当前oracle表中所有字段的类型
SELECT
      COLUMN_NAME,DATA_TYPE,DATA_LENGTH,NULLABLE
FROM
         USER_TAB_COLS where TABLE_NAME=’teacher’;
 
 
 
查询数据库存储量大小 (Master)
DECLARE @tablespaceinfo TABLE (  
    nameinfo varchar(50),  
    rowsinfo int,  
    reserved varchar(20),  
    datainfo varchar(20),  
    index_size varchar(20),  
    unused varchar(20)  
)  
 
DECLARE @tablename varchar(255);  
 
DECLARE Info_cursor CURSOR FOR 
    SELECT [name] FROM sys.tables WHERE type=’U’;  
 
OPEN Info_cursor  
FETCH NEXT FROM Info_cursor INTO @tablename  
 
WHILE @@FETCH_STATUS = 0  
BEGIN 
    insert into @tablespaceinfo exec sp_spaceused @tablename  
    FETCH NEXT FROM Info_cursor  
    INTO @tablename  
END 
 
CLOSE Info_cursor  
DEALLOCATE Info_cursor  
 
SELECT * FROM @tablespaceinfo  
    ORDER BY Cast(Replace(reserved,’KB’,”) as INT) DESC