Ms-sql database Development-Fine Canon

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