1. Sort by strokes of 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 not the same
3. Retrieve 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 hard disk partition:
EXEC master..xp_fixeddrives
5. Compare tables A and B to see if they are equal:
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print 'equal'
else
print 'not equal'
6. Kill all profiler processes:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses
WHERE program_name IN('SQL profiler',N'SQL Profiler')
EXEC sp_msforeach_worker '?'
7.Record search:
From the beginning to N records
Select Top N * From table
----------------------------------
N to M records (must have primary index ID)
Select Top MN * From table Where ID in (Select Top M ID From table) Order by ID Desc
----------------------------------
N to end record
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 fields of a table
select name from syscolumns where id=object_id('table name')
11: 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%'
12: View all stored procedures in the current database
select name as stored procedure name from sysobjects where xtype='P'
13: 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
14: Query the fields and data types of a certain table
select column_name,data_type from information_schema.columns
where table_name = 'table name'
[n].[title]:
Select * From TableName Order By CustomerName
[n].[title]:
Select * From TableName Order By CustomerNameOriginal
address:
http://www.msuniversity.edu.cn/bbs/dispbbs.asp?boardID=22&ID=392&page=1