本帖最后由 erp_zjw 于 2017-1-17 16:35 编辑
--查询用友版本号
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
--当客户的数据在其它机器上做的升级然后拷回到原机器
/*拷回的数据,通过‘系统管理’在原机器上引入后,并不会在
ufsystem数据库中的ua_account_sub这个帐套子表中回写上一年度的bClosing字段来关闭上一年度
*/
--比如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='***') --注:(***为账套号)
--科目锁定的解决
/*XX科目已经被用户[XX]锁定”
或“科目(xxxxxx)正在被机器(xxxx)上的用户(xxx)进行(xxxx)操作锁定,请稍候再试”。
*/
use UFDATA_002_2008
select ccode as 科目编码,
cauth as 功能名称,
cuser as 用户名,
cmachine as 机器名
from GL_mccontrol
delete from GL_mccontrol
-----------------------------------------------------------------
--如何取得一个数据表的所有列名
/*
方法如下:先从SYSTEMOBJECT系统表中取得数据表的SYSTEMID,然后再SYSCOLUMN表中取得该数据表的所有列名。
SQL语句如下:*/
/*(方法一*/
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
/*(方法二(邹建)*/
---跟踪程序的运行就可以了.
/*
开始--程序--MS SQLSERVER
--事件探察器(SQL Profiler)
--文件
--新建--跟踪...
--设置要跟踪的服务器的信息(连接服务器)--确定
--设置跟踪的项目...
--然后数据库的调用情况就会显示出来
在跟踪项目设置中,如果不熟悉的话,一般用默认设置
筛选项目有几个可以注意一下:
1.DatabaseName 同于你要监测的数据库名(不过这个好像不起作用,我的电脑上设置无效)
2.DatabaseID 同于你要检测的数据库的dbid,可以用 select db_id(N'你要监测的库名')得到dbid
3.ObjectName 同于你要监测的对象名,例如表名,视图名等
4.ObjectID 同于你要监测的对象的id,可以用 select object_id(N'你要监测的对象名')得到id
5.Error 同于错误,如果经常出现某个编号的错误,则针对此错误号
6.Seccess 同于0,失败,1,成功,如果是排错,就过滤掉成功的处理
*/
/*方法三:*/
--如果直接查询,可以参考我的这段代码:
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
/*--搜索某个字符串在那个表的那个字段中
--邹建 2004.10(引用请保留此信息)--*/
/*--调用示例
use pubs
exec p_search N'l'
--*/
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
-----------------------------------------------------------------
|