Some classic code in SQL database development

1. sort by strokes of the 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 -- same; otherwise, different encrypt ('original password ')

Select pwdencrypt ('original password ')

Select pwdcompare ('original password', 'encrypted password') = 1 -- 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'

Set @ SQL = 'select' + right (@ list, len (@ list)-1) + 'from table'

Exec (@ SQL)

4. View hard disk partitions:

EXEC master .. xp_fixeddrives

5. Compare whether tables A and B are equal:

If (select checksum_agg (binary_checksum (*) from)

=

(Select checksum_sum( binary_checksum (*) from B)

Print 'Equality'

Else

Print 'unequal'

6. Kill all event Inspector processes:

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. Record Search:

Starting with N records

Select Top N * From table

-------------------------------

N to M records (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 record

Select Top N * From Table Order by ID Desc

8. How to modify the Database Name:

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 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'

[N]. [title]:

Select * From TableName Order By CustomerName

[N]. [title]:

Select * From TableName Order By CustomerName