1. Sort by last name:
Select * from tablename Order by customername collate chinese_prc_stroke_ci_as
2. Database encryption:
SELECT Encrypt ('Original Password')
SELECT PWDENCRYPT ('Original Password')
SELECT PWDCompare ('Original Password', 'Encrypted Password') = 1 - The same; otherwise, not the same Encrypt ('Original Password')
SELECT PWDENCRYPT ('Original Password')
SELECT PWDCompare ('Original Password', 'Encrypted Password') = 1 - The same; otherwise it is different
3. Remove the fields in the table:
Declare @list varchar (1000), @ SQL NVARCHAR (1000)
SELECT @ list = @ list ',' B.Name from sysobjects a, syscolumns b where A.Id = B.ID and a.name = 'Table A'
Set @ SQL = 'SELECT' RIGHT (@ list, len (@List) -1) 'from Table A'
EXEC (@SQL)
4. View the hard drive partition:
Exec master..xp_fixeddrives
5. Compare the A and B tables, etc.
IF (SELECT CHECKSUM_AGG (Binary_Checksum (*)) from A)
=
(SELECT CHECKSUM_AGG (binary_checksum (*)) from b)
PRINT 'equal'
Else
Print 'is not equal.'
6. Kill all event detector processes:
Declare HcForeach Cursor Global for SELECT 'KILL' RTRIM (SPID) from master.dbo.sysprocesses
WHERE Program_name in ('SQL Profiler', N'sql Event Profiler ')
EXEC SP_MSFOREACH_WORKER '?'? '
7. Record search:
At the beginning to N records
SELECT TOP N * FROM table
-------------------------------
N to m record (must have a main index ID)
SELECT TOP M-N * FROM Table Where ID in (SELECT TOP M ID FROM Table) Order by ID DESC
----------------------------------
N to the end recording
SELECT TOP N * FROM Table Order by ID DESC
8. How to modify the name of the database:
sp_renamedb 'old_name', 'new_name'
9: Get all user tables in the current database
Select Name from sysobjects where xtype = 'u' and status> = 0
10: Get all the fields of a table
Select name from syscolumns where id = Object_id ('Name ")
11: View views related to a table, stored procedures, functions
Select a. * from sysobjects a, syscomments b where A.id = B.ID and b.text limited '% Table name% '12: View all stored procedures in the current database
SELECT NAME AS stored procedure name from sysobjects where type = 'p'
13: Query all databases created by users
Select * from master..sdatabases d where sid not in (select sid from master..slogins where name = 'sa')
or
Select DBID, Name as db_name from master..xysdatabasees where sid <> 0x01
14: Query the fields and data types of a table
Select column_name, data_type from information_schema.columns
Where table_name = 'table name
