SQL Server database practical SQL statements

-- View the foreign key constraints of a 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 numeric
Delete news where patindex ('% [0-9] %', title)> 0
-- Delete rows with duplicate field title values, and retain only the rows with a 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. sort by strokes of the Last Name:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2. Paging SQL statements
Select * from (select (row_number () OVER (order by tab. ID Desc) as rownum, tab. * from table name As tab) As t where rownum between start position And end position
3. Obtain all user tables in the current database
Select * from sysobjects where xtype = 'U' and category = 0
4. Obtain all fields in a table
Select name from syscolumns where id = object_id ('table name ')
5. View views, stored procedures, and functions related to 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 the user
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. query the fields and Data Types of a table
Select column_name, data_type from information_schema.columns
Where table_name = 'table name'
9. Use transactions
When using temporary SQL statement operations on database tables, you can use SQL SERVER transaction processing to prevent misoperation problems after data operations.
Start transaction
Begin tran
Insert Into TableName Values (...)
If the SQL statement operation is abnormal, the transaction is rolled back.
Roll back a transaction
Rollback tran
If the SQL statement is normal, the transaction is committed and the data is committed to the database.
Commit transactions
Commit tran
10. query by full-text match
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' China
11. Calculate the SQL statement query time
Declare @ d datetime
Set @ d = getdate ()
Select * from SYS_ColumnProperties select [statement execution time (MS)] = datediff (MS, @ d, getdate ())
12. Description: several advanced query Operators
A: UNION operator
The UNION operator combines two other result tables (such as TABLE1 and TABLE2) and removes any duplicate rows from the table to generate a result table. When ALL is used together with UNION (that is, union all), duplicate rows are not eliminated. In either case, each row of the derived table is from either TABLE1 or table2.
B: Random t operator
The distinct t operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When ALL is used with distinct T (distinct t all), duplicate rows are not eliminated.
C: INTERSECT Operator
The INTERSECT operator derives a result table by only including the rows in TABLE1 and TABLE2 and eliminating all repeated rows. When ALL is used with INTERSECT (intersect all), duplicate rows are not eliminated.