Data development of Classic SQL statements

Data development

1. Sort by last name stroke:
Select * from TableName Order by CustomerName Collate chinese_prc_stroke_ci_as//from less to more

2. Database encryption:
Select Encrypt (' original password ')
Select Pwdencrypt (' original password ')
Select Pwdcompare (' original password ', ' encrypted password ') = same as; otherwise different encrypt (' original password ')
Select Pwdencrypt (' original password ')
Select Pwdcompare (' original password ', ' encrypted password ') = same;

3. Retrieve the fields from the table:
DECLARE @list varchar (1000),
@sql nvarchar (1000)
Select @[email protected]+ ', ' +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 the hard disk partition:
EXEC Master. Xp_fixeddrives

5. Compare the equality of A/b table:
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 record (with 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
Case
For example 1: A table with more than 10,000 records, the first field of the table RecID is the self-growth field, write an SQL statement, find the table of the 31st to 40th Records.

Select Top RecId from a where recid not in (select top of recid from a)

Parsing: If this writes a certain problem, if RecId has a logical index in the table.

Select Top Ten recid from A where ... Is looked up from the index, and the back of select top RecId from A is looked up in the datasheet, so that the order in the index is likely to be inconsistent with the data table, which results in a query that is not the intended data.

Solution Solutions

1, with order by select top RecId from A order by ricid if the field is not self-growing, a problem occurs

2, also add condition in that subquery: Select top recid from A where recid>-1

Example 2: The last record in the query table does not know how much data the table has, and the table structure.
Set @s = ' SELECT top 1 * from T where PID isn't in (select top ' + str (@count-1) + ' pid from T ') '

Print @s exec sp_executesql @s

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

Select name from syscolumns where ID in (select id from sysobjects where type = ' u ' and name = ' table name ')

The effect is the same in both ways

11: View views, stored procedures, 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: 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 a table for fields and data types
Select Column_name,data_type from Information_schema.columns
WHERE table_name = ' table name '

15: Data manipulation between different server databases

--Create a linked server

exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '

exec sp_addlinkedsrvlogin ' itsv ', ' false ', NULL, ' username ', ' password '

--query Example

SELECT * from ITSV. Database name. dbo. Table name

--import Example

SELECT * into table from ITSV. Database name. dbo. Table name

--Remove linked server when no longer in use

exec sp_dropserver ' itsv ', ' droplogins '

--Connect remote/LAN data (Openrowset/openquery/opendatasource)

--1, OpenRowset

--query Example

SELECT * FROM OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

--Raw cost surface

SELECT * Into table from OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

--Import local tables to remote tables

Insert OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name)

Select *from Local surface

--Update the local surface

Update b

Set B. Column a=a. Column A

From OPENROWSET (' SQLOLEDB ', ' SQL Server name '; ' User name '; ' Password ', database name. dbo. Table name) as a inner join local table B

On A.column1=b.column1

--openquery usage requires creating a connection

--First create a connection to create a linked server

exec sp_addlinkedserver ' itsv ', ' ', ' SQLOLEDB ', ' Remote server name or IP address '

--Query

SELECT *

From OPENQUERY (ITSV, ' SELECT * from database. dbo. Table name ')

--Import local tables to remote tables

Insert OpenQuery (ITSV, ' SELECT * from database. dbo. Table name ')

SELECT * FROM local surface

--Update the local surface

Update b

Set B. column b=a. Column B

From OPENQUERY (ITSV, ' SELECT * from database. dbo. Table name ') as a

Inner JOIN local table B on a. Column a=b. Column A

--3, Opendatasource/openrowset

SELECT *

From OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). Test.dbo.roy_ta

--Import local tables to remote tables

Insert OpenDataSource (' SQLOLEDB ', ' Data source=ip/servername; User id= login name; password= password '). database. dbo. Table name

SELECT * FROM local surface

Original address: http://www.cnblogs.com/yubinfeng/archive/2010/11/02/1867386.html

Data development of Classic SQL statements