用友维护常用SQL语句脚本
用友维护常用SQL语句脚本（经典）（上）
用友维护人员常用SQL 脚本 --查询用友版本号 use ufsystem go select * from UA_Version go ----------------------------------------------------------------- --查看系统用户信息表 use ufsystem selec ...
|
--查询用友版本号 use ufsystem go select * from UA_Version go ----------------------------------------------------------------- --查看系统用户信息表 use ufsystem select 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_Id as 操作员编码, cUser_Name as 操作员名称 from UA_User where nState=1; --帐套主子表相关信息 use ufsystem --帐套主表 select cAcc_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 as 传真, cUnitEMail as 电子邮件, cUnitTaxNo as 税号, cUnitLP as 法人, cEntType as 企业类型, cTradeKind as 行业类型, cIsCompanyVer as 是否集团版, cDomain as 域名, cDescription 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 启用会计日期, cUser_Id as 操作员, dSubOriDate as 启用自然日期 from ua_account_sub --当客户的数据在其它机器上做的升级然后拷回到原机器 --比如002帐套结转后年度为2010,则用于关闭上一(2009)年度的sql如下： select * from ua_account_sub where cAcc_Id='002' and iYear=2008 update ua_account_sub set bclosing=0 where cAcc_Id='002' and iYear=2008 ----------------------------------------------------------------- --清除异常任务及单据锁定 use ufsystem delete from ua_task delete from ua_tasklog go delete from ufsystem..ua_task delete from ufsystem..ua_tasklog go Select * From ua_task Where(cacc_id='***') --注：（***为账套号） --科目锁定的解决 use UFDATA_002_2008 select ccode as 科目编码, cauth as 功能名称, cuser as 用户名, cmachine as 机器名 from GL_mccontrol delete from GL_mccontrol ----------------------------------------------------------------- --如何取得一个数据表的所有列名 select * from ufsystem..ua_account select * from syscolumns where id=object_id('ua_account') declare @objid int,@objname char(40) set @objname = 'ua_account' select @objid = id from sysobjects where id = object_id(@objname) select 'Column_name' = name from syscolumns where id = @objid order by colid ---跟踪程序的运行就可以了. --如果直接查询,可以参考我的这段代码: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_search]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[p_search] GO create proc p_search @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 sysobjects b on a.id=b.id where b.xtype='U' and a.status>=0 and a.xtype in(175,239,99,35,231,167) 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语句来更改用户的密码 EXEC sp_password NULL, 'newpassword', 'User' EXEC sp_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 TableName,a.name as columnname From syscolumns a INNER JOIN sysobjects b ON a.id=b.id AND b.type='U' AND a.name='cacc_id' --本例如：cacc_id列 -- b. 未知列名查所有在不同表出现过的列名 Select o.name As tablename,s1.name As columnname From syscolumns s1, sysobjects 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 * from 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日期计算 SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)) SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0)) select DATEADD(wk, DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate()) ), 0) SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0)) 1.显示本月第一天 SELECT DATEADD(mm,DATEDIFF(mm,0,getdate()),0) select convert(datetime,convert(varchar(8),getdate(),120)+'01',120) 2.显示本月最后一天 select dateadd(day,-1,convert(datetime,convert(varchar(8),dateadd(month,1,getdate()),120)+'01',120)) SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0)) 3.上个月的最后一天 SELECT dateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0)) 4.本月的第一个星期一 select DATEADD(wk,DATEDIFF(wk,0, dateadd(dd,6-datepart(day,getdate()),getdate())),0) 5.本年的第一天 SELECT DATEADD(yy,DATEDIFF(yy,0,getdate()),0) 6.本年的最后一天 SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0)) 7.去年的最后一天 SELECT dateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0)) 8.本季度的第一天 SELECT DATEADD(qq,DATEDIFF(qq,0,getdate()),0) 9.本周的星期一 SELECT DATEADD(wk,DATEDIFF(wk,0,getdate()),0) 10.查询本月的记录 select * from tableName where DATEPART(mm, theDate) = 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 DATEPART(yy, theDate) = DATEPART(yy, GETDATE()) 其中:GETDATE()是获得系统时间的函数。 13.获取当月总天数: select DATEDIFF(dd,getdate(),DATEADD(mm, 1, getdate())) select datediff(day, dateadd(mm, datediff(mm,'',getdate()), ''), dateadd(mm, datediff(mm,'',getdate()), '1900-02-01')) 14.获取当前为星期几 DATENAME(weekday, getdate()) ----------------------------------------------------------------- use ufdata_002_2008 select name from sysobjects where type='U' ----------------------------------------------------------------- --查看数据库中所有的触发器 use ufdata_002_2008 go select * from sysobjects where xtype='TR' ----------------------------------------------------------------- --查询特定数据库中某一不知归属表的触发器 use ufdata_002_2008 declare @parent_obj_id int --定义父对象id变量 --先找出父对象（所在表）的id(在触发器不重复归属于多个表的情况下) select @parent_obj_id=parent_obj from sysobjects where name='TR_Ap_CloseBills' and xtype='TR' print '所在父对象（表）的ID是：'+str(@parent_obj_id) --接下来找出父对象（表）的名称 select name as 触发器所在表为 from sysobjects where type='U' and id=@parent_obj_id ----------------------------------------------------------------- --查看触发器内容 use ufdata_002_2008 go exec sp_helptext 'TR_Ap_CloseBills' ----------------------------------------------------------------- --用于查看触发器的属性（参数指定触发器所在的表） use ufdata_002_2008 go exec sp_helptrigger Ap_CloseBills ----------------------------------------------------------------- --创建触发器 --如下是联机丛书上的一个示例，当在 titles 表上更改记录时，发送邮件通知 MaryM。 CREATE TRIGGER reminder ON titles FOR INSERT, UPDATE, DELETE AS EXEC master..xp_sendmail 'MaryM', 'Don''t forget to print a report for the distributors.' --禁用： alter table 表名 disable trigger 触发器名称 --启用： alter table 表名 enable trigger 触发器名称 ----------------------------------------------------------------- |
