首页数据库 › SQL SEXC90VEMurano 系统 表 操作

SQL SEXC90VEMurano 系统 表 操作

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

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
--调用存储进度

送货物邮件编   
nvarchar   20

('+@querystr+'State of Qatar 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

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'卡塔尔(قطر‎, 0State of Qatar 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

  where id=(select
id from sysobjects where name='订货主档'卡塔尔;

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

(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 各样数据库 客商

另外的SQL代码

declare @fdname nvarchar(250卡塔尔国--表中的主键或表、有时表中的标记列名,@id1
varchar(20卡塔尔国,@id2 varchar(20卡塔尔--开首和停止的记录号,@obj_id int
--对象id,@temp nvarchar(300卡塔尔 --有的时候语句,@strparam nvarchar(100State of Qatar--一时参数

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

select syscolumns.*,
systypes.* from syscolumns   

神速分页方法代码(sql百万级数据量分页代码卡塔尔

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

东风贸易   
dboDepartmentSalaryInfo;1   @RETU中华VN_VALUE0   4   0   NULL   0   3  
NULL   NULL   10    NULL   NULL   intint

declare @strfd nvarchar(二〇〇〇卡塔尔--复合主键列表,@strjoin
nvarchar(4000卡塔尔--连接字段,@strwhere
nvarchar(二〇〇〇卡塔尔(قطر‎--查询条件--检查输入参数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

--获得数据库中存有顾客表
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'

要货日期   
datetime   8

xtype='pk'State of Qatargoto 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)))

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连接条件

送货国家地点   
sysname    30

定此值,且不含有标记字段@fdorder nvarchar
(100卡塔尔国='',--排序字段列表@wherestr nvarchar (200卡塔尔国='', --内容是' id=3 and
model_no like '%24%'

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 '

  left join
systypes on syscolumns.xtype=systypes.xtype 

@querystr nvarchar(300State of Qatar,--表名、视图名、查询语句@pagesize
int=10,--每页的深浅(行数State of Qatar@pagecurrent int=1,--要显得的页@fdshow nvarchar
(100卡塔尔='',--要来得的字段列表,假诺查询结果有标记字段,须要指

exec TestPro 'b'

基于syscolumns获得id所对应的表

翻开字段所属表
select * from dbo.sysobjects where id in

实践结果:(字段只现身贰回,平常)

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/*--表中有复合主键的拍卖措施--*新萄京娱乐网址2492777,/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)

 

送货国家地点   
nvarchar    30

送货物邮件政编码   
sysname    20

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

 

 

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

订单日期   
datetime    8

go;

运费    money  
8

送货城市   
sysname    30

送货日期   
出华诞期类型   
8

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

要货日期   
datetime    8

送货行政区 
nvarchar    30

要货日期   
出生辰期类型   
8

4、取得表中主键所含有的列名:

客户编号   
sysname    10

实行结果:

进行结果:(部分字段现身五次,数据类型分化)

收货人 
sysname    80

送货形式   
int4

转载本站文章请注明出处:新萄京娱乐网址2492777 http://www.cdhbjs.com/?p=5378

上一篇:

下一篇:

相关文章