Oracle/SQL server notes

Reset set linesize 200 ------------------------------------ display the width of the buffer text desc user_tables; ----------------------------------- display all the table fields and structure alter table TABLE2 modify column name date; ------------------ modify the Data Type alter table TABLE2 rename column LIE3 to LIE33; ------- modify the column name alter table dbo. aaa drop constraint PK _ aaa _ 11419E22173876EA -- the simplest way to delete a primary key [SQL Server] PK _ aaa _ 11419E22173876EA The 11419E22173876EA is a hexadecimal string generated every time you add a primary key. The system data structure shows the data about www.2cto.com: Declare @ PK varChar (100 ); ----------------------------- Delete the original primary key Select @ Pk = Name from sysobjects where Parent_Obj = OBJECT_ID ('table name') and xtype = 'pk '; if @ Pk is not nullbegin exec ('alter table abcd drop' + @ Pk) endalter table TABLE1 ------------------------------------ change the column name to the primary key [SQL Server remove constraint aa] add constraint aa primary key (LIE1); Alter table TABLE1 add LIE4 number (4); ---------------- add the column name alter table TABLE2 drop column LIE3; ---------------- Delete the column name select A. TABLE_NAME, A. COLUMN_NAME, A. DATA_TYPE? ------- Display the field name and structure of A table from ALL_TAB_COLUMNS a here. TABLE_NAME = 'table name'; -- run in cmd, and then ipconfig ------------------------------ get the correct operation of ip10.68.197.72 -- Oracle on the local machine .. Drop User username; Revoke delete User set linesize 200 -------------------------------------- set window buffer port size select username from dba_users; --------------------- query all users select table_name from user_tables; ------------------- view all table names under the user create user yuan identified by orcl account unlock; ---- unlock the user create user yuan identified by yuan; ---------------- create Role role ;----------------------- --------------- Create the role grant role to yuan; ---------------------------------- grant the user permission to Create Table, Create View to role; ------------- grant the permission to Create a Table, Create a View grant resource, connect to yuan; ------------------------ authorize resources; otherwise, the table grant create session to yuan; ---------------------------- create a reply describe tables to query all table structures Oracle and SQL sewrver Common conversion functions Oracle conversion functions: www.2cto.com To_char () Convert a number to the TO_number () character to the TO_date () character format to the date Sqlserver Conversion Function char () convert a number to the character corresponding to str () the character ltrim (string) is used to clear the spaces starting from the starting position rtrim (string) in the string passed to it) it is used to clear the spaces starting from the end position in the string passed to it. getdate () returns the system's date and time truncate table T_user delete table T_user Clearing table. Pronunciation: truncate/tr ??? Ke? T/DJ /? Tr ?? Ke? T/indicates the meaning of interception. The difference between the two: delete generates rollback. For example, if you delete a large number of tables, the speed will be slow. Colleagues will occupy a lot of rollbacksegrments. truncate will not generate rollback for dll operations, and the speed will be faster. By default, Oracle performs the following tasks, the only exception to contact allocation is that all the buckets used in the deleted row are removed from the truncate part of the process by setting the minextents specified parameter. truncate can be decreased and re-created. A table dependent object requires you to add the Object Privilege on the table, it also requires you to re-create the complete new constraints of the index and store the parameters in the table trigger and respecify. trunkemts does not have these effects. -- Create role sa // www.2cto.com explain Oracle query table structure: [others' example] Explain select B. TABLE_NAME table name, c. comments Table comments, B. COLUMN_ID field signal, B. COLUMN_NAME field name, B. DATA_LENGTH Data Length, B. DATA_PRECISION integer, B. DA TA_SCALE decimal point,. comments column description from all_col_comments a, all_tab_columns B, all_tab_comments cwhere. table_name = B. TABLE_NAME and. table_name = c. table_nameand. column_name = B. COLUMN_NAME and. owner = B. OWNER and. owner = c. ownerand lower (. table_name) = 'table name' -- create user yuan identified by orcl account unlock -- grant create session to yuan -- create role name -- create Table T_a -- (-- name char (50 ), -- age int --);- -Drop user yuandesc T_a create table T_calendar (dateid number (8), dateItem date constraint nn_calendar_dateItem not null, itemcount number (3), constraint pk_calendar_calendar primary key (dateid ), constraint ck_itencount check (itencount> 0); string connectionString = ConfigurationManager. connectionStrings ["orcl"]. connectionString; www.2cto.com // connect to the database Oracle. dataAccess. client. oracleConnection connec Tion = new Oracle. dataAccess. client. oracleConnection (connectionString); try {connection. open (); Console. writeLine ("connection successful");} catch (Oracle. dataAccess. client. oracleException e) {Console. writeLine (e. message);} finally {connection. clone (); Console. writeLine ("disabled successfully! ") ;}} The author is brave enough to go forward 102002