SQL Server Database Practical SQL statement _mssql

--View FOREIGN KEY constraints for the specified table
SELECT * from sysobjects where parent_obj in (
Select id from sysobjects where name= ' table name ')
and xtype= ' PK '
--View All tables
SELECT * from sysobjects where xtype= ' PK '
--Delete a column containing numbers
Delete News where Patindex ('%[0-9]% ', title) >0
--delete the row that duplicates the title value of the field and keep only the smaller ID
Delete News where exists (select 1 from News t where T.title=news.title and T.id<news.id)
--View database information
SELECT * from sys.databases where name= ' master '
1. Order BY last name stroke:
Select * FROM TableName ORDER by CustomerName Collate Chinese_prc_stroke_ci_as
2. Paging SQL statement
SELECT * FROM (SELECT (Row_number () over () Tab.id Desc) as rownum,tab.* from table name as tab) as T where rownum between Start position and end position
3. Get all the user tables in the current database
SELECT * from sysobjects where xtype= ' U ' and category=0
4. Get all the fields of a table
Select name from syscolumns where id=object_id (' Table name ')
5. View the views, stored procedures, functions associated with a table
Select a.* from sysobjects A, syscomments b where a.id = b.ID and b.text like '% table name '
6. View all stored procedures in the current database
Select name as stored procedure name from sysobjects where xtype= ' P '
7. Query all databases created by users
SELECT * FROM Master. sysdatabases D where Sid not in (select Sid from Master. syslogins where name= ' sa ')
Or
Select dbid, name as Db_name from master. sysdatabases where Sid <> 0x01
8. Querying the fields and data types of a table
Select Column_name,data_type from Information_schema.columns
WHERE table_name = ' table name '
9. Use of transactions
SQL Server transactions can be used to prevent misoperation problems after data manipulation using a few temporary SQL statement operations on database tables
Start a transaction
Begin Tran
Insert into TableName Values (...)
If the SQL statement operation is not normal, the transaction is rolled back.
Rolling back a transaction
Rollback Tran
If the SQL statement is working correctly, the transaction is committed and the data is submitted to the database.
Commit a transaction
Commit Tran
10. Search by full text matching method
Field name like N '%[^a-za-z0-9]china[^a-za-z0-9]% '
OR field name like N '%[^a-za-z0-9]china '
OR field name like N ' china[^a-za-z0-9]% '
OR field name like N '
11. Calculate query time for executing SQL statements
DECLARE @d datetime
Set @d=getdate ()
SELECT * FROM sys_columnproperties SELECT [Statement execution takes time (milliseconds)]=datediff (Ms,@d,getdate ())
12, Description: Several advanced query operation words
A:union operator
The UNION operator derives a result table by combining the other two result tables (such as TABLE1 and TABLE2) and eliminating any duplicate rows in the table. When all is used with union (that is, union ALL), duplicate rows are not eliminated. In both cases, each row of the derived table is either from TABLE1 or from TABLE2.
B:except operator
The EXCEPT operator derives a result table by including all rows that are in TABLE1 but not in TABLE2, and all duplicate rows are eliminated. When all is used with EXCEPT (EXCEPT all), duplicate rows are not eliminated.
C:intersect operator
The INTERSECT operator derives a result table by including only the rows in TABLE1 and TABLE2 and eliminates all duplicate rows. When all is used with INTERSECT (INTERSECT all), duplicate rows are not eliminated.