抛砖引玉：使用SYBASE过程中积累的一些生语句的语句

sybase12

--把数据从一个库插入到另一个库，两个库结构相同。
select 'print '+"'"+name+"'"+'
go'+'
insert DATABASE1..'+name+' select * from DATABASE2..'+name+'
go'
from sysobjects
where type='U'
order by name

--更换某库固定数据类型的字段成另一种类型
select         'print '+"'"+a.name+'.'+b.name+"'"+'
go'+'
alter table '+a.name+'
modify '+b.name+' float '+'
go'

from sysobjects a,syscolumns b
where a.id=b.id
and b.usertype=8 and length=4
order by a.name,b.colid



--更新所有表的统计值，并且做一致性检查
select 'dbcc checktable('+name+')'+'
go'+'
update statistics '+name+'
go'
from sysobjects where type='U'
order by name


--查询包含某个特定的字符的字段在什么表中出现过
select a.name,b.name
from sysobjects a,syscolumns b
where a.id=b.id
and a.type='U'
and b.name like '%DJJSBJ%'  --特定字符
order by a.name


--查询所有用户表，所有列的数据类型和长度
select         a.id,a.name name_table,b.colid,b.name name_column,
        c.usertype,c.name name_usertype,b.length length_true,c.length length_sys
from sysobjects a,syscolumns b,systypes c
where a.id=b.id
and b.usertype=c.usertype
and a.type='U'
and b.length<>;c.length
order by a.name,b.colid


select * from syscolumns where id=705434556

select * from systypes order by type

--生成BCP语句
select 'bcp NCR_ZD..'+CONVERT(CHAR(30),name)+'out c:\wzy\ncr_zd\'+ CONVERT(CHAR(30),name)+' -n -Usa -Pitsapsa -STEST_CS'
from sysobjects where type='U'
order by name

select 'bcp NCR_FD..'+CONVERT(CHAR(30),name)+'out c:\wzy\ncr_fd\'+ CONVERT(CHAR(30),name)+' -n -Usa -Pitsapsa -STEST_CS'
from sysobjects where type='U'
order by name

--生语句：删除所有表（慎用）
select 'drop table '+convert(char(30),name)
from sysobjects where type='U'
order by name

--生语句：清空所有表（慎用）
select 'truncate table '+convert(char(30),name)+'
go'+'
update statistics '+name+'
go'
from sysobjects where type='U'
order by name


--生语句：显示各表记录数
select 'print '+"'"+name+"'"+'
select count(*) from '+convert(char(30),name)
from sysobjects where type='U'
order by name

--显示各表记录数
SELECT indid,a.name,b.rowcnt
from sysobjects a,systabstats b
where a.id=b.id
and a.type='U'
and b.indid in(0,1) --0，表；1，allpage(表级锁)锁定表的集群索引。。。。。。（还是不太明白）
order by a.name

--SYBASE系统表说明中说：1表示统计信息是升级的结果（不是update statistics 的结果）。
--直到今天也还不太明白，希望各位高人能点拨一二

--查询记录数为0 的表
SELECT 'drop table '+a.name+'
go'
from sysobjects a,systabstats b
where a.id=b.id
and a.type='U'
and b.indid=0
and b.rowcnt=0
order by a.name

SELECT 'select count(*) from '+a.name+'
go'
from sysobjects a,systabstats b
where a.id=b.id
and a.type='U'
and b.indid=0
and b.rowcnt=0
order by a.name

--产生按时间删除、插入的语句
select * from syscolumns

select 'delete '+convert(char(30),a.name)+'where '+convert(char(30),b.name)+'>;='+"'2003.01.11'"+'
go'
from sysobjects a,syscolumns b
where a.id=b.id
and b.type=61
and a.type='U'

--生语句：所有表的SELECT
select 'print '+"'"+name+"'"+'
select * from '+name+ ' where 1=2'
from sysobjects
where type='U'
order by name

--生语句：部分表的全部字段插入
select 'print '+"'"+'insert FD_JXC..'+name+"'"+'
select * from '+name+ ' where 1=2'+'
print '+"'"+'FROM NCR_FD..'+name+"'"+'
print '+"'"+'GO'+"'"
from sysobjects
where type='U'
and name in
('CLIENT_BONUS',
'DISC_SALES',
'DISC_TICKET',
'INVENTORY',
'INVENTORY_COUNT',
'INVENTORY_ITEMS',
'PRICE_AUDIT',
'REGION_PURCHASE_MON',
'SECTION_STAT',
'SHIFT')
order by name


--复杂BCP语句,

select 'echo '+name+' >;d:\bcp\bm.txt'+'
bcp ML_JXC..'+CONVERT(CHAR(30),name)+'out d:\bcp\szml\'+ CONVERT(CHAR(30),name)+' -n -Usa -P -SSZML >;D:\bcp\r1.TXT'+'
COPY d:\bcp\r.txt+d:\bcp\bm.txt+d:\bcp\r1.txt d:\bcp\r.txt'
from sysobjects where type='U'
order by name

欢迎追加，交流

一只散步的鱼

查询所有行锁表表名
select * from sysobjects where sysstat2>;163840 and type="U"

查询全页锁表表名
select * from sysobjects where sysstat2<81920 and type="U"

查询全页锁中的大表
select b.name,a.pagecnt/512 as "size" from systabstats a,sysobjects b where a.id=b.id and b.type="U" and a.pagecnt/512>;0 and b.sysstat2<81920
order by a.pagecnt/512 desc

按表大小进行查询
select b.name,a.pagecnt/512 as "size" from systabstats a,sysobjects b where a.id=b.id and b.type="U" and a.pagecnt/512>;0
order by a.pagecnt/512 desc

sybase12

欢迎追加

樂

学ing

xdf118

好东东