Common Summary of SQL Server database development

1. sort by the last name of zookeeper:
Select * From tablename order by customername collate chinese_prc_stroke_ci_as
2. Data Encryption:
Select encrypt ('original secret ')
Select pwdencrypt ('original secret ')
Select pwdcompare ('original password secret', 'encrypted password secret') = 1 -- same; otherwise different encrypt ('original password secret ')
Select pwdencrypt ('original secret ')
Select pwdcompare ('original password secret', 'encrypted password secret') = 1 -- same; otherwise different
3. Retrieve the tablespace 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'
Set @ SQL = 'select' + right (@ list, Len (@ list)-1) + 'from table'
Exec (@ SQL)
4. view the hard drive partition:
Exec master .. xp_fixeddrives
5. Is table B equal to table A and table B:
If (select checksum_agg (binary_checksum (*) from)
=
(Select checksum_sum( binary_checksum (*) from B)
Print 'Equality'
Else
Print 'unequal'
6. Cancel all event progress probes:
Declare hcforeach cursor global for select 'Kill '+ rtrim (spid) from Master. DBO. sysprocesses
Where program_name in ('SQL filer', n' SQL event profiler ')
Exec sp_msforeach_worker '? '
7. Remember to search:
Start to N records
Select Top N * from table
-------------------------------
N to M (primary index ID required)
Select top M-N * from table where ID in (select top m id from Table) order by ID DESC
----------------------------------
N to the end
Select Top N * from Table order by ID DESC
8. How to modify the name of a resource:
Sp_renamedb 'old _ name', 'new _ name'
9: retrieve all the tables used in the current data volume
Select name from sysobjects where xtype = 'U' and status> = 0
10: retrieve all the bitwise of a table
Select name from syscolumns where id = object_id ('table name ')
11. view the stored procedures, 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 resource
Select name as stored procedure parameter from sysobjects where xtype = 'P'
13: check all the resources created by the website
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 bitwise AND data type of a table.
Select column_name, data_type from information_schema.columns
Where table_name = 'table name'
[N]. [subject]:
Select * From tablename order by customername
[N]. [subject]:
Select * From tablename order by customername

Original article address ﹕
Http://www.msuniversity.edu.cn/bbs/dispbbs.asp? Boardid = 22 & id = 392 & page = 1