SQL database development code

1. sort by surname strokes: 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 -- same; otherwise, they are different.

3. retrieve the table fields: declare @ list varchar (1000), @ SQL nvarchar (1000) Select @ list = @ list + ',' + B. name from sysobjects A, syscolumns B where. id = B. ID and. name = 'table a' set @ SQL = 'select' + right (@ list, Len (@ list)-1) + 'from table a' exec (@ SQL)

 

4. view the hard disk partition: exec master .. xp_fixeddrives

 

5. compare whether tables A and B are equal: If (select checksum_agg (binary_checksum (*) from a) = (select checksum_agg (binary_checksum (*) from B) print 'equal to 'else print 'not equal'

 

6. kill all event Inspector 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. take records at the specified position: -- select Top N * from Table ----------------------------------- -- N to M records (Master Index ID is required) select top M-N * from table where ID in (select top m id from Table) order by id desc ---------------------------------- -- N to the end record select Top N * from Table order by ID DESC

 

8. Modify the Database Name: sp_renamedb 'old _ name', 'new _ name'

 

9: obtain all user tables in the current database. Select name from sysobjects where xtype = 'U' and status> = 0

 

10: obtain all the fields of a table. Select name from syscolumns where id = object_id ('table name ')

 

11: View views, stored procedures, and function select a related to a table. * From sysobjects A, syscomments B where. 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 the 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 fields and Data Types of a table. Select column_name, data_type from information_schema.columns where table_name = 'table name'