MS-SQL server database development classic skills_PHP tutorial

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 different
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. Check the 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 '?'
'www.knowsky.com
7. Record search:
Start to N records
Select Top N * From table
----------------------------------
N to M records (must have primary index ID)
Select Top M-N * 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 certain 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