Data Development-classic and data development classic

Data Development-classic and data development classic
Data Development-classic

1. sort by strokes of the Last Name:

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') = 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 @ listvarchar (1000), @ SQL nvarchar (1000)

Select @ list = @ list + ',' + B. name from sysobjects a, syscolumns B wherea. id = B. id and. 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 whether tables A and B are equal:

If (selectchecksum_agg (binary_checksum (*) from)

=

(Select checksum_sum( binary_checksum (*) from B) print 'equal to 'else print 'not equal'

6. Kill all event Inspector processes:

DECLARE hcforeach cursorglobal 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 the table Where ID in (Select Top M IDFrom table) Order by ID Desc

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

N to the end record

Select Top N * From Table Order by ID Desc

Case

Example 1:

A table has more than 10 thousand records. The first RecID field of the table is an auto-increment field. Write an SQL statement to find the 31st to 40th records of the table.

Select top 10 recid from A where recidnot in (select top 30 recid from)

Analysis: If this write method is used, some problems may occur. If the recid has a logical index in the table.

Select top 10 recid from A where ...... Is to search from the index, while selecttop 30 recid from A is to search in the data table. As A result, the order in the index may be different from that in the data table, in this way, the queried data is not originally expected.

Solution

1. Use orderSelecttop 30 recid from A order by ricidIf this field is not self-incrementing, the problem may occur.

2. Add the following conditions to the subquery:Selecttop 30 recid from A where recid>-1

Example 2: query the last record in the table and do not know how much data the table has and the table structure. Set @ s = 'select top 1 * from T where pid not 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 wherextype = '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 two methods have the same effect.

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 wherextype = '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 .. sysdatabaseswhere sid <> 0x01

14: query the fields and Data Types of a table

Select column_name, data_typefrom information_schema.columns where table_name = 'table name'

15: Data operations between databases on different servers

-- Create a linked server

Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'

Exec sp_add1_srvlogin '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

-- Delete the linked server when it is no longer in use

Exec sp_dropserver 'itsv', 'droplogins'

 

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

-- 1. openrowset

-- Query example

Select * from openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table name)

-- Generate a local table

Select * into table from openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table name)

 

-- Import a local table to a remote table

Insert openrowset ('sqlodb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table name)

Select * from local table

-- Update local table

Update B

Set B. Column A = a. Column

Fromopenrowset ('sqloledb', 'SQL Server name'; 'username'; 'Password', database name. dbo. Table Name) as a inner join local Table B

On a. column1 = B. column1

-- Create a connection for openquery usage

-- First create a connection to create a linked server

Exec sp_addrole server 'itsv', '', 'sqloledb', 'remote server name or IP address'

-- Query

Select *

FROM openquery (ITSV, 'select * FROM database. dbo. Table name ')

-- Import a local table to a remote table

Insert openquery (ITSV, 'select * FROM database. dbo. Table name ')

Select * from local table

-- Update local table

Update B

Set B. Column B = a. Column B

FROM openquery (ITSV, 'select * FROM database. dbo. Table name') asa

Inner join local table B on a. Column A = B. Column

 

-- 3. opendatasource/openrowset

SELECT *

FROM opendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password'). test. dbo. roy_ta

-- Import a local table to a remote table

Insert opendatasource ('sqlodb', 'Data Source = ip/ServerName; User ID = login name; Password = password'). database. dbo. Table Name

Select * from local table