Home > Backend Development > PHP Tutorial > MS-SQL server database development classic skills_PHP tutorial

MS-SQL server database development classic skills_PHP tutorial

WBOY
Release: 2016-07-13 17:02:04
Original
766 people have browsed it

1. Sort by strokes of 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--the same; otherwise not the same encrypt('original password')
select pwdencrypt('original password')
select pwdcompare(' Original password','encrypted password') = 1--the 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 A'
set @sql='select ' right(@list, len(@list)-1) ' from table A'
exec (@sql)
4. Check the hard disk partition:
EXEC master..xp_fixeddrives
5. Compare tables A and B to see if they are equal :
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print 'Equal'
else
print 'not equal'
6. Kill all profiler 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 '?'
'www.knowsky.com
7. Record search:
Start to N records
Select Top N * From table
----------------------------------
N to M records (must have primary index ID)
Select Top M-N * From table Where ID in (Select Top M ID From table) Order by ID Desc
--------------------- -------------
N to 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 user tables in the current database
select Name from sysobjects where xtype='u' and status>=0
10: Get all fields of a certain 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 users

www.bkjia.comtruehttp: //www.bkjia.com/PHPjc/631081.htmlTechArticle1. Sort by strokes of 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...
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template