澳门新萄京迅猛分页方法代码(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’State of Qatar=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
      )
     )
    State of Qatar 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卡塔尔(قطر‎=”,–要显示的字段列表,假设查询结果有标记字段,须求指

=============================================
获得MS SQL库数据词典的经文SQL语句

1.到手具有数据库名:
   (1)、Select Name FROM
Master..SysDatabases order by Name
2.**得到具有表名:

定此值,且不分包标志字段@fdorder nvarchar
(100卡塔尔=”,–排序字段列表@wherestr nvarchar (200卡塔尔(قطر‎=”, –内容是’ id=3 and
model_no like ‘%24%’

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

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

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

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 ‘

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

declare @fdname nvarchar(250卡塔尔国–表中的主键或表、有的时候表中的标记列名,@id1
varchar(20卡塔尔(قطر‎,@id2 varchar(20卡塔尔–初叶和终止的记录号,@obj_id int
–对象id,@temp nvarchar(300卡塔尔国 –有时语句,@strparam nvarchar(100State of Qatar–不常参数

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’State of Qatar–每种关系表名,能够用or连接条件

           注意:经常景况只须求type =
‘U’,但临时会有系统表混在个中(不知晓怎么来头),加上前边一句后就能够去除这一个系统表了

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

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

3.**获得具备字段名:**
(1)、Select Name FROM SysColumns Where
id=Object_Id(‘TableName’)

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

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
–调用存款和储蓄进度

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

(‘+@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卡塔尔State of Qatarexec(‘select top ‘+@id1+@fdshow+’
from
‘+@querystr+@wherestr+@fdorderState of Qatarreturnend–如若是表,则检查表中是不是有标志更或主键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

exec TestPro ‘b’

故事syscolumns得到id所对应的表

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

       注意点:
     (a)这里为了主要特出有些首要内容,选拔了中间几项新闻输出。
     (b)syscolumns表中只包罗数据类型编号,要得到完整的名字要求从systypes表中找,平时客户接受的数据类型用xusertype对应比较好,不会现出有的多的场所。
     (c)syscolumns.length获得的是概略内部存款和储蓄器的长短,所以nvarchar和varchar等品类在数据库中的突显是其一的八分之四。

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_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’卡塔尔国,0State of Qatar,
允许空=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

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图