Some classical code in the development of SQL database

1. Order BY last name stroke:

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;

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

print ' equal '

Else

print ' Not Equal '

6. Kill all the 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 Records

Select Top N * FROM table

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

N to M Records (to have the primary index ID)

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 name of the database:

Sp_renamedb ' Old_name ', ' new_name '

9: Get all the user tables in the current database

Select Name from sysobjects where xtype= ' u ' and status>=0

10: Get all the fields of a table

Select name from syscolumns where id=object_id (' Table name ')

11: View the views, stored procedures, functions associated with 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: Querying 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: Querying 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