Some classic code in SQL database development

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.