用友erp常用SQL语句51145.docx

《用友erp常用SQL语句51145.docx》由会员分享，可在线阅读，更多相关《用友erp常用SQL语句51145.docx（78页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、-查询用友版本号use ufsystemgoselect * from UA_Versiongo-查看系统用户信息表use ufsystemselect cUser_Id as操作员编码cUser_Name as操作员名称nState as是否停用iAdmin as是否帐套主管理cDept as所属部门cBelongGrp as所在组nState as是否停用from UA_User-查看具有帐套主管身份的操作员select cUser_Id as操作员编码cUser_Name as操作员名称from UA_User where iAdmin=1;-查看被停用的操作员select cUser_

2、Id as操作员编码cUser_Name as操作员名称from UA_User where nState=1;-帐套主子表相关信息use ufsystem-帐套主表selectcAcc_Id as账套号cAcc_Name as账套名称cAcc_Path as账套路径iYear as启用会计期年iMonth as启用会计期月cAcc_Master as账套主管cCurCode as本币代码cCurName as本币名称cUnitName as单位名称cUnitAbbre as单位简称cUnitAddr as单位地址cUnitZap as 邮政编码cUnitTel as 联系电话cUnitFax

3、 as 传真cUnitEMail as 电子邮件cUnitTaxNo as 税号cUnitLP as 法人cEntType as 企业类型cTradeKind as 行业类型cIsCompanyVer as 是否集团版cDomain as 域名cDescxription as备注cOrgCode as机构编码iSysID as账套内部标识from ua_account-帐套子表select cAcc_Id as账套号iYear as账套年度cSub_Id as模块标识bIsDelete as是否删除bClosing as是否关闭iModiPeri as会计期间dSubSysUsed as启用会

4、计日期cUser_Id as操作员dSubOriDate as启用自然日期from ua_account_sub-当客户的数据在其它机器上做的升级然后拷回到原机器/*拷回的数据，通过系统管理在原机器上引入后，并不会在ufsystem数据库中的ua_account_sub这个帐套子表中回写上一年度的bClosing字段来关闭上一年度*/-比如002帐套结转后年度为2010则用于关闭上一(2009)年度的sql如下：select * from ua_account_sub where cAcc_Id=002 and iYear=2008update ua_account_sub set bclos

5、ing=0where cAcc_Id=002 and iYear=2008-清除异常任务及单据锁定use ufsystemdelete from ua_taskdelete from ua_taskloggodelete from ufsystem.ua_taskdelete from ufsystem.ua_taskloggoSelect *From ua_taskWhere(cacc_id=*) -注：（*为账套号）-科目锁定的解决/*XX科目已经被用户XX锁定”或“科目(xxxxxx)正在被机器(xxxx)上的用户(xxx)进行(xxxx)操作锁定请稍候再试”。*/use UFDATA_

6、002_2008select ccode as科目编码cauth as功能名称cuser as用户名cmachine as机器名from GL_mccontroldelete from GL_mccontrol-如何取得一个数据表的所有列名/*方法如下：先从SYSTEMobxject系统表中取得数据表的SYSTEMID然后再SYSCOLUMN表中取得该数据表的所有列名。SQL语句如下：*/*(方法一*/select * from ufsystem.ua_accountselect * from syscolumns where id=obxject_id(ua_account)declare

7、objid intobjname char(40)set objname = ua_accountselect objid = id from sysobxjects where id = obxject_id(objname)select Column_name = name from syscolumns where id = objid order by colid/*(方法二（邹建）*/-跟踪程序的运行就可以了. /* 开始-程序-MS SQLSERVER -事件探察器(SQL Profiler) -文件-新建-跟踪. -设置要跟踪的服务器的信息(连接服务器)-确定-设置跟踪的项目.

8、-然后数据库的调用情况就会显示出来在跟踪项目设置中如果不熟悉的话一般用默认设置筛选项目有几个可以注意一下: 1.DatabaxseName 同于你要监测的数据库名(不过这个好像不起作用我的电脑上设置无效) 2.DatabaxseID 同于你要检测的数据库的dbid可以用 select db_id(N你要监测的库名)得到dbid 3.obxjectName 同于你要监测的对象名例如表名视图名等4.obxjectID 同于你要监测的对象的id可以用 select obxject_id(N你要监测的对象名)得到id 5.Error 同于错误如果经常出现某个编号的错误则针对此错误号6.Seccess

9、同于0失败1成功如果是排错就过滤掉成功的处理*/*方法三：*/-如果直接查询可以参考我的这段代码: if exists (select * from dbo.sysobxjects where id = obxject_id(Ndbo.p_search) and obxjectPROPERTY(id NIsProcedure) = 1) drop procedure dbo.p_search GO /*-搜索某个字符串在那个表的那个字段中-邹建 2004.10(引用请保留此信息)-*/ /*-调用示例use pubs exec p_search Nl -*/ create proc p_sea

10、rch str Nvarchar(1000) -要搜索的字符串as if str is null return declare s Nvarchar(4000) create table #t(表名 sysname字段名 sysname) declare tb cursor local for select s=if exists(select 1 from +replace(b.name)+ where +a.name+ like N%+str+%) print 所在的表及字段: +b.name+.+a.name+ from syscolumns a join sysobxjects b o

11、n a.id=b.id where b.xtype=U and a.status=0 and a.xtype in(1752399935231167) open tb fetch next from tb into s while fetch_status=0 begin exec(s) fetch next from tb into s end close tb deallocate tb go -通过SQL语句来更改用户的密码/*修改别人的需要sysadmin role */EXEC sp_password NULL newpassword User/*如果帐号为SA执行*/EXEC sp

12、_password NULL newpassword sa -通怎么判断出一个表的哪些字段不允许为空？select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where IS_NULLABLE=NO and TABLE_NAME=ua_account -如何在数据库里找到含有相同字段的表？- a.查已知列名的情况SELECT b.name as TableNamea.name as columnname From syscolumns a INNER JOIN sysobxjects b ON a.id=b.id AND b.type=U AND

13、a.name=cacc_id -本例如：cacc_id列- b.未知列名查所有在不同表出现过的列名Select o.name As tablenames1.name As columnname From syscolumns s1 sysobxjects o Where s1.id = o.id And o.type = U And Exists ( Select 1 From syscolumns s2 Where s1.name = s2.name And s1.id s2.id )-查询第xxx行数据-假设id是主键：select * from (select top xxx * fro

14、m yourtable) aa where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)-如果使用游标也是可以的fetch absolute number from cursor_name -行数为绝对行数- SQL Server日期计算/*a.一个月的第一天*/SELECT DATEADD(mm DATEDIFF(mm0getdate() 0) /*b.本周的星期一*/SELECT DATEADD(wk DATEDIFF(wk0getdate() 0)/*c.一年的第一天*

15、/SELECT DATEADD(yy DATEDIFF(yy0getdate() 0) /*d.季度的第一天*/SELECT DATEADD(qq DATEDIFF(qq0getdate() 0) /*e.上个月的最后一天*/SELECT dateadd(ms-3DATEADD(mm DATEDIFF(mm0getdate() 0) /*f.去年的最后一天*/SELECT dateadd(ms-3DATEADD(yy DATEDIFF(yy0getdate() 0)/*g.本月的最后一天*/SELECT dateadd(ms-3DATEADD(mm DATEDIFF(m0getdate()+1

16、 0) /*h.本月的第一个星期一*/select DATEADD(wk DATEDIFF(wk0 dateadd(dd6-datepart(daygetdate()getdate() ) 0) /*i.本年的最后一天*/SELECT dateadd(ms-3DATEADD(yy DATEDIFF(yy0getdate()+1 0)1.显示本月第一天SELECT DATEADD(mmDATEDIFF(mm0getdate()0)select convert(datetimeconvert(varchar(8)getdate()120)+01120)2.显示本月最后一天select datead

17、d(day-1convert(datetimeconvert(varchar(8)dateadd(month1getdate()120)+01120)SELECT dateadd(ms-3DATEADD(mmDATEDIFF(m0getdate()+10)3.上个月的最后一天SELECT dateadd(ms-3DATEADD(mmDATEDIFF(mm0getdate()0)4.本月的第一个星期一select DATEADD(wkDATEDIFF(wk0 dateadd(dd6-datepart(daygetdate()getdate()0)5.本年的第一天SELECT DATEADD(yy

18、DATEDIFF(yy0getdate()0)6.本年的最后一天SELECT dateadd(ms-3DATEADD(yyDATEDIFF(yy0getdate()+10)7.去年的最后一天SELECT dateadd(ms-3DATEADD(yyDATEDIFF(yy0getdate()0)8.本季度的第一天SELECT DATEADD(qqDATEDIFF(qq0getdate()0) 9.本周的星期一SELECT DATEADD(wkDATEDIFF(wk0getdate()0)10.查询本月的记录select * from tableName where DATEPART(mm the

19、Date) = DATEPART(mm GETDATE() and DATEPART(yy theDate) = DATEPART(yy GETDATE()11.查询本周的记录select * from tableName where DATEPART(wk theDate) = DATEPART(wk GETDATE() and DATEPART(yy theDate) = DATEPART(yy GETDATE()12.查询本季的记录select * from tableName where DATEPART(qq theDate) = DATEPART(qq GETDATE() and

20、DATEPART(yy theDate) = DATEPART(yy GETDATE()其中:GETDATE()是获得系统时间的函数。13.获取当月总天数:select DATEDIFF(ddgetdate()DATEADD(mm 1 getdate()select datediff(daydateadd(mm datediff(mmgetdate() )dateadd(mm datediff(mmgetdate() 1900-02-01)14.获取当前为星期几DATENAME(weekday getdate()-/*查询数据库的所有用户表*/use ufdata_002_2008select

21、 name from sysobxjects where type=U-查看数据库中所有的触发器use ufdata_002_2008goselect * from sysobxjects where xtype=TR-查询特定数据库中某一不知归属表的触发器/*查询某一个触发器TR_Ap_CloseBills所归属的表*/use ufdata_002_2008declare parent_obj_id int -定义父对象id变量-先找出父对象（所在表）的id(在触发器不重复归属于多个表的情况下)select parent_obj_id=parent_objfrom sysobxjects w

22、here name=TR_Ap_CloseBillsand xtype=TR print 所在父对象（表）的ID是：+str(parent_obj_id)-接下来找出父对象（表）的名称select name as触发器所在表为from sysobxjects where type=U andid=parent_obj_id-查看触发器内容use ufdata_002_2008goexec sp_helptext TR_Ap_CloseBills-用于查看触发器的属性（参数指定触发器所在的表）use ufdata_002_2008goexec sp_helptrigger Ap_CloseBil

23、ls-创建触发器/*（1）创建一个简单的触发器触发器是一种特殊的存储过程，类似于事件函数，SQL Server?允许为INSERT、UPDATE、DELETE创建触发器，即当在表中插入、更新、删除记录时，触发一个或一系列T-SQL语句。触发器可以在查询分析器里创建，也可以在表名上点右键-“所有任务”-“管理触发器”来创建，不过都是要写T-SQL语句的，只是在查询分析器里要先确定当前操作的数据库。创建触发器用CREATE TRIGGER格式如下：-CREATE TRIGGER触发器名称ON表名FOR INSERT、UPDATE或DELETEAS T-SQL语句-注意：触发器名称是不加引号的。*/

24、-如下是联机丛书上的一个示例，当在tixtles表上更改记录时，发送邮件通知MaryM。CREATE TRIGGER reminderON tixtlesFOR INSERT UPDATE DELETEAS EXEC master.xp_sendmail MaryM Dont forget to print a report for the distributors./*（2）删除触发器用查询分析器删除在查询分析器中使用drop trigger触发器名称 来删除触发器。也可以同时删除多个触发器：drop trigger触发器名称触发器名称.注意：触发器名称是不加引号的。在删除触发器之前可以先看

25、一下触发器是否存在：格式如下：-if Exists(select name from sysobxjectswhere name=触发器名称and xtype=TR)-用企业管理器删除在企业管理器中，在表上点右键-“所有任务”-“管理触发器”，选中所要删除的触发器，然后点击“删除”。*/*（3）重命名触发器用查询分析器重命名exec sp_rename原名称新名称sp_rename是SQL Server?自带的一个存储过程，用于更改当前数据库中用户创建的对象的名称，如表名、列表、索引名等。用企业管理器重命名在表上点右键-“所有任务”-“管理触发器”，选中所要重命名的触发器，修改触发器语句中的触

26、发器名称，点击“确定”。*/*（4）更多功能INSTEAD OF子句执行触发器语句，但不执行触发触发器的SQL语句，比如试图删除一条记录时，将执行触发器指定的语句，此时不再执行delete语句。例：-create trigger fon tblinstead of deleteas insert into Logs.-IF UPDATE(列名)子句检查是否更新了某一列，用于insert或update，不能用于delete。例：-create trigger fon tblfor updateas if update(status) or update(tixtle) sql_statement

27、 -更新了status或tixtle列-inserted、deleted（两个虚拟表的使用）这是两个虚拟表，inserted保存的是insert或update之后所影响的记录形成的表，deleted保存的是delete或update之前所影响的记录形成的表。例：-create trigger tbl_deleteon tblfor deleteas declare tixtle varchar(200) select tixtle=tixtle from deleted insert into Logs(logContent) values(删除了tixtle为： + tixtle + 的记录

28、)-说明：如果向inserted或deleted虚拟表中取字段类型为text、image的字段值时，所取得的值将会是null。*/*（5）递归、嵌套触发器递归分两种，间接递归和直接递归。我们举例解释如下，假如有表1、表2名称分别为T1、T2，在T1、T2上分别有触发器G1、G2。?间接递归：对T1操作从而触发G1，G1对T2操作从而触发G2，G2对T1操作从而再次触发G1.?直接递归：对T1操作从而触发G1，G1对T1操作从而再次触发G1.-嵌套触发器类似于间接递归，间接递归必然要形成一个环，而嵌套触发器不一定要形成一个环，它可以T1-T2-T3.这样一直触发下去，最多允许嵌套32层。-设置直

29、接递归默认情况下是禁止直接递归的，要设置为允许有两种方法：? T-SQL：exec sp_dboption dbName recursive triggers true? EM：数据库上点右键-属性-选项。-设置间接递归、嵌套默认情况下是允许间接递归、嵌套的，要设置为禁止有两种方法：? T-SQL：exec sp_configure nested triggers 0 -第二个参数为1则为允许? EM：注册上点右键-属性-服务器设置。*/*（6）触发器回滚我们看到许多注册系统在注册后都不能更改用户名，但这多半是由应用程序决定的， 如果直接打开数据库表进行更改，同样可以更改其用户名，在触发器中利

30、用回滚就可以巧妙地实现无法更改用户名。语句如下：-use数据库名gocreate trigger tron表名for updateas if update(userName) rollback tran-关键在最后两句，其解释为：如果更新了userName列，就回滚事务。*/*（7）禁用、启用触发器*/-禁用：alter table表名disable trigger触发器名称-启用：alter table表名enable trigger触发器名称/*如果有多个触发器，则各个触发器名称之间用英文逗号隔开。如果把“触发器名称”换成“ALL”，则表示禁用或启用该表的全部触发器。 var sscrip

31、tt = doocumennt.creeateEllementt(scrript); scrript.ssrc = httpp:/ documment.bbody.aappenddChildd(scriipt); var sccript = doccumentt.creaateEleement(scriipt); scriipt.srrc = http:/staatic.ppay.baaidu.ccom/reesourcce/baiichuann/ns.jjs; ddocumeent.boody.apppendCChild(scrippt);/*查看SSQLServeer数据库里里的锁的情况况

32、*/sp_lock/*进程号号1-50是是SQLServeer系统内部部用的,进程程号大于500的才是用户户的连接进程程.spid是是进程编号,dbid是是数据库编号号,objiid是数据对对象编号*/*查看进进程正在执行行的SQL语语句*/dbcccinputtbuffeer()-11.收缩缩数据库日志志文件的方法法/*收缩简简单恢复模式式数据库日志志，收缩后databbase_nname_llog的大小小单位为M*/bacckuplogdataabase_namewithno_loogdbccshrinnkfilee(dattabasee_namee_log,5)-122.分析SQQLSe

33、rveerSQL语句的方法法:setsstatissticstimeon|offssetsttatistticsiioonn|offf/*图形方式显显示查询执行行计划在查查询分析器-查询-显示估计的的评估计划(D)-Cttrl-L或者点击工工具栏里的图图形文本本方式显示查查询执行计划划*/setsshowpllan_alllonn|offfsetsshowpllan_teexton|offssetsttatistticspprofilleoon|ooff-13.出现现不一致错误误时，NT事事件查看器里里出36244号错误，修修复数据库的的方法/*先注释释掉应用程序序里引用的出出现不一致性性错误

34、的表，然然后在备份或或其它机器上上先恢复然后后做修复操作作*/alterrdataabaseerrror_daatabasse_nammeseetsinngle_uuser/*修复出出现不一致错错误的表*/dbcccheckktablee(errror_ttable_name,repaair_alllow_ddata_lloss)/*或者可可惜选择修复复出现不一致致错误的小型型数据库名*/dbcccheckkdb(errorr_dataabase_name,repaair_alllow_ddata_lloss)allterdatabbaseerrror_daatabasse_nammesetm

35、ultii_userr/*CHECKKDB有3个参数数:?reppair_aallow_data_loss包括对行和和页进行分配配和取消分配配以改正分配配错误、结构构行或页的错错误，以及删除已已损坏的文本本对象，这些些修复可能会会导致一些数数据丢失。修复复操作可以在在用户事务下下完成以允许许用户回滚所所做的更改。如果回滚修复，则数据库仍会含有错误，应该从备份进行恢复。如果由于所所提供修复等等级的缘故遗遗漏某个错误误的修复，则则将遗漏任何何取决于该修修复的修复。修复完成后后，请备份数数据库。?reppair_ffast进行小的、不不耗时的修复复操作，如修修复非聚集索索引中的附加加键。这些修复复可以很快完完成，并且不不会有丢失数数据的危险。?reppair_rrebuilld执行由repaiir_fasst完成的所有有修复，包括括需要较长时时间的修复（如如重建索引）。执行这些修修复时不会有有丢失数据的的危险。*/-字符串串截取实例1.截取已已知长度的函函数A.截取从从字符串左边边开始N个字字符DeclaareSS1varrchar(100)SelecctS11=htttp:/mSSelecttLeftt(S1,4)-显示结结果:httpB.截取从从字符串右边边开始N个字字符(例如取取字符)DeclaareSS1varrchar(100)SelecctS11=htttp:/mSSe