Some fine code in the development of SQL database

1. Sort by last Name: SELECT * FROM TableName ordered 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--same;

3. Retrieve the field in the table: Declare @list varchar (1000), @sql nvarchar (1000) Select @list = @list + ', ' +b.name from sysobjects a,syscolumns b WH ere 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 partitions: exec master ... Xp_fixeddrives

5. Compare A,b tables for equality: if (select Checksum_agg (binary_checksum (*)) from A) = (select Checksum_agg (binary_checksum (*)) from B) PR int ' equal ' else print ' not equal '

6. Kill all Event Explorer 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: start to n record select top n * FROM table-------------------------------N to M record (to 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: Gets 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 view, stored procedure, function 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 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

14: Query the field and data type of a table select Column_name,data_type from information_schema.columns WHERE table_name = ' table name '.