UF Maintenance Common SQL statement script

UF Maintenance Common SQL Statement Script (Classic) (top) UF maintenance Staff Common SQL script--Query the UF version number use Ufsystem go select * from Ua_version go------------------------------ -------------------------------------View System User Information Sheet use Ufsystem Selec ...

UF maintenance personnel commonly used SQL script --Query the UF version number Use Ufsystem Go SELECT * FROM Ua_version Go ----------------------------------------------------------------- --View System User Information table Use Ufsystem Select cuser_id as operator code, Cuser_name as operator name, Nstate as is deactivated, Iadmin as is the main management of the account set, Cdept as belongs to the department, Cbelonggrp as group, Nstate as is deactivated From Ua_user --View the operator with the Account manager status Select cuser_id as operator code, Cuser_name as operator name from Ua_user where iadmin=1; --View deactivated operators Select cuser_id as operator code, Cuser_name as operator name from Ua_user where nstate=1; --related information of the master table of the account set Use Ufsystem --The main table of the account set Select cacc_id as Account set number, Cacc_name as Account set name, Cacc_path as Account set path, Iyear as enables accounting annuity, Imonth as enables accounting months, Cacc_master as Account Director, Ccurcode as local currency code, Ccurname as local currency name, Cunitname as unit name, Cunitabbre as unit abbreviation, CUNITADDR as unit address, Cunitzap as post code, Cunittel as contact number, Cunitfax as Fax, Cunitemail as email, Cunittaxno as tax number, CUNITLP as legal person, Centtype as Enterprise type, Ctradekind as Industry type, Ciscompanyver as whether the group version, Cdomain as domain name, Cdescription as notes, Corgcode as mechanism code, Isysid as account sleeve internal identification From Ua_account --Account List Select cacc_id as Account set number, Iyear as account set year, csub_id as module identification, Bisdelete as is deleted, Bclosing as is off, Imodiperi as accounting period, Dsubsysused as enables fiscal dates, cuser_id as operator, Dsuboridate as enable natural date From Ua_account_sub --When the customer's data is upgraded on another machine and then copied back to the original machine -for example, 002 post-closing year is 2010, then the SQL used to close the previous (2009) year is as follows: 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 ----------------------------------------------------------------- --Clear Exception task and document lock 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= ' * * * ')--NOTE: (* * * for the account set number) --the solution of the subject lock Use ufdata_002_2008 Select Ccode as account code, Cauth as function name, Cuser as user name, CMachine as machine name From Gl_mccontrol Delete from Gl_mccontrol ----------------------------------------------------------------- --How to get all the column names of a data table 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 ---the tracking program to run. --If you query directly, you can refer to my code: 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)--the string to search for As If @str is null return DECLARE @s Nvarchar (4000) CREATE table #t (table name sysname, field name sysname) Declare TB cursor Local for Select S= ' If exists (select 1 from [' +replace "(b.name, '] ', ']] ') + ' WHERE [' +a.name+ '] like N '% '[email protected]+ '% ') The table and field where print ' is located: [' +b.name+ ']. [' +a.name+ '] From syscolumns a joins 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 to @s While @ @fetch_status =0 Begin EXEC (@s) FETCH NEXT from TB to @s End Close TB Deallocate TB Go ----------------------------------------------------------------- --change user's password by SQL statement EXEC sp_password NULL, ' newpassword ', ' User ' EXEC sp_password NULL, ' NewPassword ', SA ----------------------------------------------------------------- --How to determine which fields of a table are not allowed to be empty? Select column_name from INFORMATION_SCHEMA. COLUMNS where is_nullable= ' NO ' and table_name= ' Ua_account ' ----------------------------------------------------------------- --How do I find a table with the same fields in the database? --A. Check for known column names 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 '--This example: cacc_id column --B. Unknown column names all columns that appear in different tables 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 ) ----------------------------------------------------------------- --Query Line XXX data --Suppose the ID is the primary key: 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) --If using a cursor is also possible Fetch absolute [number] from [cursor_name] --The number of rows is the absolute number of rows ----------------------------------------------------------------- --SQL Server Date Calculation 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. Show the first day of the month SELECT DATEADD (Mm,datediff (Mm,0,getdate ()), 0) Select CONVERT (Datetime,convert (varchar (8), GETDATE (), 120) + ' 01 ', 120) 2. Show last day of the month 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. Last day of last month SELECT DateAdd (Ms,-3,dateadd (Mm,datediff (Mm,0,getdate ()), 0)) 4. First Monday of the month Select DATEADD (Wk,datediff (wk,0, DATEADD (Dd,6-datepart (Day,getdate ()), GETDATE ()), 0) 5. First day of the year SELECT DATEADD (Yy,datediff (Yy,0,getdate ()), 0) 6. Last day of the year SELECT DateAdd (Ms,-3,dateadd (Yy,datediff (Yy,0,getdate ()) +1,0) 7. Last day of last year SELECT DateAdd (Ms,-3,dateadd (Yy,datediff (Yy,0,getdate ()), 0)) 8. The first day of the quarter SELECT DATEADD (Qq,datediff (Qq,0,getdate ()), 0) 9. This week's Monday SELECT DATEADD (Wk,datediff (Wk,0,getdate ()), 0) 10. Check this month's records SELECT * from TableName where DATEPART (mm, thedate) = DATEPART (mm, GETDATE ()) and DATEPART (yy, thedate) = DATEPART (yy, GET DATE ()) 11. Query this week's records SELECT * from TableName where DATEPART (wk, thedate) = DATEPART (wk, GETDATE ()) and DATEPART (yy, thedate) = DATEPART (yy, GET DATE ()) 12. Check the records for this season SELECT * from TableName where DATEPART (QQ, thedate) = DATEPART (QQ, GETDATE ()) and DATEPART (yy, thedate) = DATEPART (yy, GET DATE ()) Where: GETDATE () is a function of acquiring system time. 13. Get the total number of days in the month: 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. Get the current day of the week Datename (Weekday, GETDATE ()) ----------------------------------------------------------------- Use ufdata_002_2008 Select name from sysobjects where type= ' U ' ----------------------------------------------------------------- --View all triggers in the database Use ufdata_002_2008 Go SELECT * from sysobjects where xtype= ' TR ' ----------------------------------------------------------------- --Query for a trigger in a particular database that does not know the attribution table Use ufdata_002_2008 DECLARE @parent_obj_id INT--Define Parent object ID variable --First find the ID of the parent object (in the case where the trigger does not return to more than one table) Select @parent_obj_id =parent_obj From sysobjects where name= ' tr_ap_closebills ' and xtype= ' TR ' The ID of the parent object (table) where print ' resides is: ' +str (@parent_obj_id) --Next find the name of the parent object (table) The table for the select name as Trigger is From sysobjects where type= ' U ' and[email protected]_obj_id ----------------------------------------------------------------- --View trigger content Use ufdata_002_2008 Go exec sp_helptext ' tr_ap_closebills ' ----------------------------------------------------------------- --Used to view the properties of the trigger (parameter specifies the table in which the trigger is located) Use ufdata_002_2008 Go EXEC sp_helptrigger Ap_closebills ----------------------------------------------------------------- --Create a trigger --The following is an example on Books Online that sends an email notification marym when a record is changed on the titles table. 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. ' --Disable: ALTER TABLE name disable TRIGGER trigger name --Enable: ALTER TABLE name enable TRIGGER trigger name -----------------------------------------------------------------

UF Maintenance Common SQL statement script