用友维护人员常用SQL语句.doc

用友维护人员常用SQL语句（下）数据库加密:select encrypt(原始密码)select pwdencrypt(原始密码)select pwdcompare(原始密码,加密后密码) = 1-相同；否则不相同 encrypt(原始密码)select pwdencrypt(原始密码)select pwdcompare(原始密码,加密后密码) = 1-相同；否则不相同-取回表中字段:declare list varchar(1000),sql nvarchar(1000) select list=list+,+ from sysobjects a,syscolumns b where a.id=b.id and =表Aset sql=select +right(list,len(list)-1)+ from 表A exec (sql)-查看硬盘分区:EXEC master.xp_fixeddrives-比较A,B表是否相等:if (select checksum_agg(binary_checksum(*) from A) = (select checksum_agg(binary_checksum(*) from B)print 相等elseprint 不相等-杀掉所有的事件探察器进程:DECLARE hcforeach CURSOR GLOBAL FOR SELECT kill +RTRIM(spid) FROM master.dbo.sysprocessesWHERE program_name IN(SQL profiler,NSQL 事件探查器)EXEC sp_msforeach_worker ?-记录搜索:开头到N条记录Select Top N * From 表-N到M条记录(要有主索引ID)Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by IDDesc-N到结尾记录Select Top N * From 表 Order by ID Desc-修改数据库的名称:sp_renamedb old_name, new_name -获取当前数据库中的所有用户表select Name from sysobjects where xtype=u and status=0-获取某一个表的所有字段select name from syscolumns where id=object_id(表名)-查看与某一个表相关的视图、存储过程、函数select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like %表名%-查看当前数据库中所有存储过程select name as 存储过程名称 from sysobjects where xtype=P-查询用户创建的所有数据库select * from master.sysdatabases D where sid not in(select sid from master.syslogins where name=sa)或者select dbid, name AS DB_NAME from master.sysdatabases where sid 0x01-查询某一个表的字段和数据类型select column_name,data_type from information_schema.columnswhere table_name = 表名 n.标题:Select * From TableName Order By CustomerName n.标题:Select * From TableName Order By CustomerName -建表时自动建立主键约束：create table a(b char(4),c int,constraint 主键名 primary key(c) -或者：create table a(a char(4) cint primary key) -或者：create table a(a char(4) cint constraint 主键名 primary key) -创建: create table 表名 add constraint 约束名 unique 约束条件 -修改: alter table 表名 add constraint 约束名 unique 约束条件 -删除: alert table 表名 drop constraint 约束名 如不知道或忘记了约束可用sp_help 来查询 -创建索引：create index 索引名 on 表名(字段名) -为一个表添加外键：alter table 表名 add constraint 外键名 foreign key(字段）references 表名（字段） -创建一个标识列identity(seed,range) 创建一个标识列，与null,not null并列。seed表示种子，即初始值；range表示增长幅度。-使用一个数据库之前要引用他：use 数据库名 -exists()判断子查询的结果是否存在，返回true or false-object_id(对象名) 返回该对象名对应的Id,该id存储在sysobjects表中。-局部临时表 #xxx ，只能被当前会话访问，在该会话结束后自动消失。 -全局临时表 #xx , 可以供多个用户使用，在该会话结束后自动消失。- 为表 添加/修改/删除列 ：alter table 表名 add 列名 type/alter column 列名 newtype/drop 列名-用系统过程 sp_reZGXX_XM 重新命名表和列： sp_reZGXX_XM 原表名，新表名 sp_reZGXX_XM 表名.原列名，新列名-如果想显示的在identity列中插入值，则需要先设置：set identity_insert 表名 on 插入完毕后最好设置：set identity_insert 表名 off-truncate table 表名 删除表格的所有数据，速度很快。-统计函数中除了count(*) 之外，都忽略空值（null).-由于text和image类型数据很长，在查询之前可以通过设置全局变量textsize来指定返回数据的长度，set textsize 50 如果想查阅全局变量textsize的值：select textsize-通过reaDtext 读取text的数据： declare var varbinary(16) select var=textptr(c) from test where a=10 readtext test.c var 4 3-利用writetext往text 或 image列中写入值： declare var varbinary(16) select var=textptr(c) from test where a=10 writetext test.c var zhongguo - like 也是唯一可以在text列上使用的操作符 - 逻辑操作符优先级：NOT AND OR-在group by 一个记录集时，所有的null组成一组。 -带有group by 子句的 select中可以有where子句，但是where 子句必须放在group by 前面。 -如果group by 子句中用了all,即 group by all xxx ,则不符合检索条件的记录也显示，但不参与统计。-having 中，只能包含 group by子句中 指定的列，也可以包含统计函数。where中可指定任何列，但是不能用统计函数-having 子句从最终结果中将不满足该条件的分组去掉-不带group by子句时也可以使用having子句，并将整个查询结果作为一个组，但是，由于出现在选择列表中的列 和出现在having子句中的列必须是group by 子句中的列，所以，当不带group by子句时，不能在having子句和 选择列表中直接使用列名，只能使用统计函数。-当在group by子句后指定order by子句时，只能在order by子句中指定group by子句中的列或者统计函数 -在进行union运算时，自动删除结果中的重复行，如果使用all选项 ，则可以将所有行显示在结果中：union all-在union时，合并结果集中的列名有第一个查询给出，所以后面进行排旬时一定要注意order by 子句中的字段名-可以通过 select fieldslist into 新表名 from 表名，来创建一个新表，并将当前表中的数据全部插入到新 表中，但是做这个操作之前需要保证数据库选项 select into/bulkcopy 设置为true。方法如下： use master /*设置命令必须在master数据库中进行*/ sp_dboption 数据库a名，select into/bulkcopy,true /*设置数据库选项*/ use 数据库a名 checkpoint /*使设置结果生效*/-如果要将统计结果或者计算结果插入到新表中，必须以标题的形式给出列名，如： select a,b=avg(c) into mm from nn group by a -随即取出N条记录的方法：select top N * from 表名 order by newid()-创建唯一约束：create table a(b int not nul constraint 约束名 unique,c char(10) null) 或者：create table a(b int,c char(10),constraint 约束名 unique(b)-为变量赋值方法：set xxx=? ，如果变量的值取自一个查询的话，需要用select, 如：select xxx=? from ? where ? 如果要返回一个记录集，但是不是从一个表格，而是全部是系统变量或自定义变量组成。则不必写from子句：select ?,?,?-创建一个返回一张表的函数： create function fn_Tree(Id int) returns table tb (id int ,fid int) as begin insert tb select id,fid from tablename where fid=id while exists (select 1 from tablename where fid in (select id from tb) and id not in (select id from tb) ) insert tb select id,fid from tablename where fid in (select id from tb) and id not in (select id from tb) return end 表的字段为id,fid insert tb select 语句，将查询结果插入到当前的表格(tb)中 调用： select * from dbo.fn_Tree(0) go select * from dbo.fn_Tree(1 go-删除表Drop table命令用于删除一个表格或者表中的所有行。其语法格式为：drop table tablename 下面举个例子：drop table employee; 为了删除整个表（包括所有的行），可以使用drop table命令后加上tablename。Drop table命令跟从表中删除所有记录是不一样的：删除表中的所有记录是留下表格（只是它是空的）以及约束信息；而drop table是删除表的所有信息，包括所有行、表格以及约束信息等等。 -升级问题解决方案如果升级失败，请先打开升级日志：U8安装目录Admin下文件名为 UFDATA_+账套号+_+年度.txt，查看详细的错误信息；如果升级提示错误为错误信息:-2147217900表示SQL Server 此时无法获取 LOCK 资源。请在活动用户数较少时重新运行您的语句，或者请求系统管理员检查 SQL Server 锁和内存配置。解决办法： 打开SQL Server查询分析器，在Master中运行以下语句：sp_configure locks,2147483647reconfigurewith override重启动Server。在升级之前，建议先在查询分析器中执行 DBCC CHECKDB(年度库名称) 语句，检查年度库数据库是否有一致性错误，如果发现错误，请按照SQL Server的提示进行修复，修复后再进行升级。-数据库置疑 及帐套年结情况分析:年度数据库物理文件一般存放在下面命名规则的文件夹下X:U8SOFTAdmin服务器名ZT帐套号年度年度数据库文件命名：数据库名为：UFDATA_帐套号_年度逻辑文件名物理文件名-Ufmodel UFDATA.MDF -数据文件Ufmodel_LOG UFDATA.LDF -日志文件附：=另外对于帐套而言，每个帐套还需要一下文件数据库名为：UFMeta_帐套号逻辑文件名物理文件名-UFMeta UFMeta.mdf -数据文件UFMeta_LogUFMeta.ldf -日志文件直接拷贝覆盖与分离后附加是不一样的，不要直接拷贝覆盖（还会出现质疑）另外，对于置疑和正常数据库不要在sql企业管理器中直接右键删除，否则数据库物理文件就没有了且无痕迹use ufsystem-查看帐套信息select * from ua_account-查看某帐套年度帐信息select * from ua_account_sub where cacc_ID=010-查看某帐套年结情况(bclosing为1表示已结)-说明：如果是在其他机器上捉的年结直接拷贝过来的话，不会回写ua_account_sub表的bclosing字段，就需要手工加上select cacc_id as 帐套,iyear as 年度,bclosing as 是否年结 from ua_account_sub where cacc_ID=010-查看所有数据库（位置，大小，使用情况）sp_helpdb-查看指定数据库位置，大小，使用情况）sp_helpdb ufdata_001_2010select * FROM UA_userupdate UA_user set cPassword=null where cUser_Name=demo-查询用户创建的所有数据库select * from master.sysdatabases D where sid not in(select sid from master.syslogins where name=sa)-或者select dbid, name AS DB_NAME from master.sysdatabases where sid 0x01-查询所有数据库select * from master.sysdatabases-将数据库附加到服务器。EXEC sp_attach_db dbname = NUFDATA_009_2008, filename1 = ND:U8SOFTADMINSERVER1ZT0092008UFMeta.mdf, filename2 = ND:U8SOFTADMINSERVER1ZT0092008UFMeta.ldf-将数据库从服务器分离。EXEC sp_detach_db UFDATA_009_2008, true用友维护人员常用经典SQL语句（中）-SQL Server 数据库管理常用的SQL和T-SQL -1. 查看数据库的版本 select version -2. 查看数据库所在机器操作系统参数 exec master.xp_msver -3. 查看数据库启动的参数 sp_configure -4. 查看数据库启动时间 select convert(varchar(30),login_time,120) from master.sysprocesses where spid=1 /*查看数据库服务器名和实例名 */print Server Name.: + convert(varchar(30),SERVERNAME) print Instance.: + convert(varchar(30),SERVICENAME) -5. 查看所有数据库名称及大小 sp_helpdb /*查看某个特定数据库的名称及大小及存储位置*/exec sp_helpdb UFDATA_002_2008/*重命名数据库用的SQL */sp_renamedb old_dbname, new_dbname -6. 查看所有数据库用户登录信息 sp_helplogins /*查看所有数据库用户所属的角色信息 */sp_helpsrvrolemember /*修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程 */*更改某个数据对象的用户属主 */sp_changeobjectowner objectname = object, newowner = owner /*注意: 更改对象名的任一部分都可能破坏脚本和存储过程。 把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本 */-7. 查看链接服务器 sp_helplinkedsrvlogin /*查看远端数据库用户登录信息 */sp_helpremotelogin -8.查看某数据库下某个数据对象的大小 sp_spaceused objname /*还可以用sp_toptables过程看最大的N(默认为50)个表 */*查看某数据库下某个数据对象的索引信息 */sp_helpindex objname /*还可以用SP_NChelpindex过程查看更详细的索引情况 */SP_NChelpindex objname /*clustered索引是把记录按物理顺序排列的，索引占的空间比较少。 对键值DML操作十分频繁的表我建议用非clustered索引和约束，fillfactor参数都用默认值。 */*查看某数据库下某个数据对象的的约束信息 */sp_helpconstraint objname -9.查看数据库里所有的存储过程和函数 use database_name sp_stored_procedures /*查看存储过程和函数的源代码 */sp_helptext procedure_name /*查看包含某个字符串str的数据对象名称 */select distinct object_name(id) from syscomments where text like %str% /*创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数 解密加密过的存储过程和函数可以用sp_decrypt过程*/-10.查看数据库里用户和进程的信息 sp_who /*查看SQL Server数据库里的活动用户和进程的信息 */sp_who active /*查看SQL Server数据库里的锁的情况 */sp_lock /*进程号1-50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程. spid是进程编号,dbid是数据库编号,objid是数据对象编号 */*查看进程正在执行的SQL语句 */dbcc inputbuffer () -11.收缩数据库日志文件的方法 /*收缩简单恢复模式数据库日志，收缩后database_name_log的大小单位为M */backup log database_name with no_log dbcc shrinkfile (database_name_log, 5) -12.分析SQL Server SQL 语句的方法: set statistics time on | off set statistics io on | off /*图形方式显示查询执行计划 在查询分析器-查询-显示估计的评估计划(D)-Ctrl-L 或者点击工具栏里的图形 文本方式显示查询执行计划 */set showplan_all on | off set showplan_text on | off set statistics profile on | off -13.出现不一致错误时，NT事件查看器里出3624号错误，修复数据库的方法 /*先注释掉应用程序里引用的出现不一致性错误的表，然后在备份或其它机器上先恢复然后做修复操作 */alter database error_database_name set single_user /*修复出现不一致错误的表 */dbcc checktable(error_table_name,repair_allow_data_loss) /*或者可惜选择修复出现不一致错误的小型数据库名 */dbcc checkdb(error_database_name,repair_allow_data_loss) alter database error_database_name set multi_user /*CHECKDB 有3个参数: ? repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误， 以及删除已损坏的文本对象，这些修复可能会导致一些数据丢失。 修复操作可以在用户事务下完成以允许用户回滚所做的更改。 如果回滚修复，则数据库仍会含有错误，应该从备份进行恢复。 如果由于所提供修复等级的缘故遗漏某个错误的修复，则将遗漏任何取决于该修复的修复。 修复完成后，请备份数据库。 ? repair_fast 进行小的、不耗时的修复操作，如修复非聚集索引中的附加键。 这些修复可以很快完成，并且不会有丢失数据的危险。 ? repair_rebuild 执行由 repair_fast 完成的所有修复，包括需要较长时间的修复（如重建索引）。 执行这些修复时不会有丢失数据的危险。*/-字符串截取实例1.截取已知长度的函数A.截取从字符串左边开始N个字符Declare S1 varchar(100)Select S1=Select Left(S1,4)-显示结果: httpB.截取从字符串右边开始N个字符(例如取字符)Declare S1 varchar(100)Select S1=Select right(S1,11)-显示结果: C.截取字符串中任意位置及长度(例如取字符www)Declare S1 varchar(100)Select S1=Select SUBSTRING(S1,8,3)-显示结果: www以上例子皆是已知截取位置及长度,下面介绍未知位置的例子2.截取未知位置的函数A.截取指定字符串后的字符串(例如截取http:/后面的字符串)方法一:Declare S1 varchar(100)Select S1=Select Substring(S1,CHARINDEX(www,S1)+1,Len(S1)/*此处也可以这样写:Select Substring(S1,CHARINDEX(/,S1)+2,Len(S1)*/ -显示结果: 需要注意:CHARINDEX函数搜索字符串时,不区分大小写,因此CHARINDEX(www,S1)也可以写成CHARINDEX(WWW,S1)方法二与方法一类似)Declare S1 varchar(100)Select S1=Select Substring(S1,PATINDEX(%www%,S1)+1,Len(S1)-此处也可以这样写:Select Substring(S1,PATINDEX(%/%,S1)+2,Len(S1)-显示结果: 函数PATINDEX与CHARINDEX区别在于:前者可以参数一些参数,增加查询的功能方法三:Declare S1 varchar(100)Select S1=Select REPLACE(S1,http:/,)-显示结果: 利用字符替换函数REPLACE,将除需要显示字符串外的字符替换为空方法四:Declare S1 varchar(100)Select S1=Select STUFF(S1,CHARINDEX(http:/,S1),Len(http:/),)-显示结果: 函数STUFF与REPLACE区别在于:前者可以指定替换范围,而后者则是全部范围内替换B.截取指定字符后的字符串(例如截取C:Windowstest.txt中文件名) 与A不同的是,当搜索对象不是一个时,利用上面的方法只能搜索到第一个位置方法一:Declare S1 varchar(100)Select S1=C:Windowstest.txtselect right(S1,charindex(,REVERSE(S1)-1)-显示结果: text.txt说明：利用函数REVERSE获取需要截取的字符串长度用友维护人员常用SQL 脚本-查询用友版本号use ufsystemgoselect * from UA_Versiongo-查看系统用户信息表use ufsystemselectcUser_Id as 操作员编码,cUser_Name as 操作员名称,nState as 是否停用 ,iAdmin as 是否帐套主管理,cDept as 所属部门,cBelongGrp as 所在组,nState as 是否停用from UA_User-查看具有帐套主管身份的操作员selectcUser_Id as 操作员编码,cUser_Name as 操作员名称from UA_User where iAdmin=1;-查看被停用的操作员selectcUser_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 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=2008update ua_