Common Summary of MS-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
