Convert a long date to a short date Convert (char (10), getdate (), 120)
MS-SQL database development commonly used Summary 1. sort by surname strokes:
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; 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'
Set @ SQL = 'select' + right (@ list, len (@ list)-1) + 'from table'
Exec (@ SQL)
4. View hard disk partitions:
EXEC master .. xp_fixeddrives
5. Compare whether tables A and B are equal:
If (select checksum_agg (binary_checksum (*) from)
=
(Select checksum_sum( binary_checksum (*) from B)
Print 'Equality'
Else
Print 'unequal'
6. Kill all event Inspector processes:
DECLARE hcforeach cursor global 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 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 Database Name:
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 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 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: query 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]:
From http://dev.csdn.net/develop/article/83/83138.shtm
1. copy only one table structure without copying data
Select top 0 * into [t1] from [t2]
2. Obtain the creation script of an object in the database
1. Use the following script to create a function.
If exists (select 1 from sysobjects where id = object_id ('fgetscript') and objectproperty (id, 'isinlinefunction ') = 0)
Drop function fgetscript
Go
Create function fgetscript (
@ Servername varchar (50) -- server name
, @ Userid varchar (50) = 'sa '-- user name. If it is an nt authentication method, it is null.
, @ Password varchar (50) = ''-- password
, @ Databasename varchar (50) -- Database Name
, @ Objectname varchar (250) -- Object Name
) Returns varchar (8000)
As
Begin
Declare @ re varchar (8000) -- returns the script
Declare @ srvid int, @ dbsid int -- defines the server and database Set id
Declare @ dbid int, @ tbid int -- database and table id
Declare @ err int, @ src varchar (255), @ desc varchar (255) -- error handling variable
-- Create an sqldmo object
Exec @ err = sp_oacreate 'sqldmo. sqlserver ', @ srvid output
If @ err <> 0 goto lberr
-- Connect to the server
If isnull (@ userid, '') ='' -- if it is an Nt authentication method
Begin
Exec @ err = sp_oasetproperty @ srvid, 'loginsecure ', 1
If @ err <> 0 goto lberr
Exec @ err = sp_oamethod @ srvid, 'connect ', null, @ servername
End
Else
Exec @ err = sp_oamethod @ srvid, 'connect ', null, @ servername, @ userid, @ password
If @ err <> 0 goto lberr
-- Obtain the database set
Exec @ err = sp_oagetproperty @ srvid, 'databases', @ dbsid output
If @ err <> 0 goto lberr
-- Obtain the database id of the script
Exec @ err = sp_oamethod @ dbsid, 'item', @ dbid output, @ databasename
If @ err <> 0 goto lberr
-- Obtain the Object id of the script
Exec @ err = sp_oamethod @ dbid, 'getobjectbyname', @ tbid output, @ objectname
If @ err <> 0 goto lberr
-- Obtain the script
Exec @ err = sp_oamethod @ tbid, 'script', @ re output
If @ err <> 0 goto lberr
-- Print @ re
Return (@ re)
Lberr:
Exec sp_oageterrorinfo NULL, @ src out, @ desc out
Declare @ errb varbinary (4)
Set @ errb = cast (@ err as varbinary (4 ))
Exec master .. xp_varbintohexstr @ errb, @ re out
Set @ re = 'error code: '+ @ re
+ Char (13) + 'error Source: '+ @ src
+ Char (13) + 'error Description:' + @ desc
Return (@ re)
End
Go
2. Usage:
The usage is as follows,
Print dbo. fgetscript ('server name', 'username', 'Password', 'database name', 'table name or other object name ')
3. To obtain scripts for all objects in the library, use the following method:
Declare @ name varchar (250)
Declare # aa cursor
Select name from sysobjects where xtype not in ('s ', 'pk', 'D', 'x', 'L ')
Open # aa
Fetch next from # aa into @ name
While @ fetch_status = 0
Begin
Print dbo. fgetscript ('onlytiancai ', 'sa', 'sa ', 'database', @ name)
Fetch next from # aa into @ name
End
Close # aa
Deallocate # aa
4. declare that this function is provided by the csdn leader
3. Separator string
If there is a string separated by commas (,), for example, "a, B, c, d, 1, 2, 3, 4", how to use t-SQL to obtain several elements of the string, what is the value of the element to be retrieved? Because t-SQL does not have the split function or the array concept, you can only write several functions by yourself.
1. Function for retrieving the number of elements
Create function getstrarrlength (@ str varchar (8000 ))
Returns int
As
Begin
Declare @ int_return int
Declare @ start int
Declare @ next int
Declare @ location int
Select @ str = ',' + @ str + ','
Select @ str = replace (@ str ,',,',',')
Select @ start = 1
Select @ next = 1
Select @ location = charindex (',', @ str, @ start)
While (@ location <> 0)
Begin
Select @ start = @ location + 1
Select @ location = charindex (',', @ str, @ start)
Select @ next = @ next + 1
End
Select @ int_return = @ next-2
Return @ int_return
End
2. Function for obtaining the value of a specified index
Create function getstrofindex (@ str varchar (8000), @ index int = 0)
Returns varchar (8000)
As
Begin
Declare @ str_return varchar (8000)
Declare @ start int
Declare @ next int
Declare @ location int
Select @ start = 1
Select @ next = 1 -- if you are used to starting from 0, select @ next = 0
Select @ location = charindex (',', @ str, @ start)
While (@ location <> 0 and @ index> @ next)
Begin
Select @ start = @ location + 1
Select @ location = charindex (',', @ str, @ start)
Select @ next = @ next + 1
End
If @ location = 0 select @ location = len (@ str) + 1 -- if there is no comma to exit, the comma is considered after the string
Select @ str_return = substring (@ str, @ start, @ location-@ start) -- @ start must be the position after the comma or be the initial value 1.
If (@ index <> @ next) select @ str_return = ''-- if the two are not equal, it is because there are too few commas, or @ index is less than the initial value of @ next.
Return @ str_return
End
3. Test
SELECT [dbo]. [getstrarrlength] ('1, 2, 3, 4, a, B, c, D ')
SELECT [dbo]. [getstrofindex] ('1, 2, 3, 4, a, B, c, D', 5)
4. statement execution spans several databases
What should I do if I want to operate different tables in different databases on different servers in one statement?
Method 1:
Select * from OPENDATASOURCE ('sqlodb', 'Data Source = remote ip address; User ID = sa; Password = password'). Database Name. dbo. Table Name
Method 2:
Use the connection server first:
EXEC sp_addmediaserver 'Alias ', '', 'msdasql', NULL, NULL, 'driver = {SQL Server}; SERVER = remote name; UID = user; PWD = password ;'
Exec sp_add1_srvlogin @ rmtsrvname = 'aliased ', @ useself = 'false', @ locallogin = 'sa', @ rmtuser = 'sa ', @ rmtpassword = 'Password'
GO
Then you can:
Select * from alias. Database Name. dbo. Table Name
Insert database name. dbo. Table Name select * from alias. Database Name. dbo. Table Name
Select * into database name. dbo. New table name from alias. Database Name. dbo. Table Name
Go
5. How to obtain information about all fields in a table
Frog frog recommendation: how to obtain information about all fields in a table
Create a view first
Create view fielddesc
As
Select o. name as table_name, c. name as field_name, t. name as type, c. length
Length, c. isnullable as isnullable, convert (varchar (30), p. value) as desp
From syscolumns c
Join policypes t on c. xtype = t. xusertype
Join sysobjects o on o. id = c. id
Left join sysproperties p on p. smallid = c. colid and p. id = o. id
Where o. xtype = 'U'
Query:
Select * from fielddesc where table_name = 'your table name'
There is also a stronger statement, which is written by the producer. Please write it.
SELECT
(Case when a. colorder = 1 then d. name else ''end) n' table name ',
A. colorder n' Field Sequence Number ',
A. name n' field name ',
(Case when COLUMNPROPERTY (a. id, a. name, 'isidentity ') = 1 then' √ 'else' 'end) n' ',
(Case when (SELECT count (*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a. id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a. id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a. id) AND (name = a. name) AND
(Xtype = 'pk')> 0 then' √ 'else' end) n'primary key ',
B. name n' type ',
A. length N 'number of bytes occupied ',
COLUMNPROPERTY (a. id, a. name, 'precision ') as N 'length ',
Isnull (COLUMNPROPERTY (a. id, a. name, 'Scale'), 0) as N 'decimal ',
(Case when a. isnullable = 1 then '√ 'else' 'end) n' allow null ',
Isnull (e. text, '') n' default value ',
Isnull (g. [value], '') AS n' field description'
-- Into # tx
FROM syscolumns a left join policypes B
On a. xtype = B. xusertype
Inner join sysobjects d
On a. id = d. id and d. xtype = 'U' and d. name <> 'dtproperties'
Left join syscomments e
On a. cdefault = e. id
Left join sysproperties g
On a. id = g. id AND a. colid = g. smallid
Order by object_name (a. id), a. colorder
Vi. Time Format Conversion
Because the newly developed software requires some data generated by some old software, the time format is not uniform and can only be converted manually. Three statements were written one afternoon, I have never used convert functions and case statements before, and the "+" operator also plays different roles in different context environments, so I am confused, but now it seems that it is almost done.
1. Change all "70.07.06" to "1970-07-06"
UPDATE lvshi
SET shengri = '19' + REPLACE (shengri ,'.','-')
WHERE (zhiyezheng = '20140901 ')
2. Extract "70", "07", "06" from "1970-07-06"
Select substring (shengri, 3, 2) AS year, SUBSTRING (shengri, 6, 2) AS month,
SUBSTRING (shengri, 9, 2) AS day
FROM lvshi
WHERE (zhiyezheng = '20140901 ')
3. convert a time field to "1970-07-06"
UPDATE lvshi
SET shenling = CONVERT (varchar (4), YEAR (shenling ))
+ '-' + Case when len (MONTH (shenling) = 1 THEN '0' + CONVERT (varchar (2 ),
Month (shenling) else convert (varchar (2), month (shenling ))
END + '-' + case when len (day (shenling) = 1 THEN '0' + CONVERT (char (2 ),
Day (shenling) else convert (varchar (2), day (shenling) END
WHERE (zhiyezheng = '20140901 ')
VII. Partition View
Partition view is a good way to improve query performance
-- See the following example.
-- Example Table
Create table tempdb. dbo. t_10 (
Id int primary key check (id between 1 and 10), name varchar (10 ))
Create table pubs. dbo. t_20 (
Id int primary key check (id between 11 and 20), name varchar (10 ))
Create table northwind. dbo. t_30 (
Id int primary key check (id between 21 and 30), name varchar (10 ))
Go
-- Partition View
Create view v_t
As
Select * from tempdb. dbo. t_10
Union all
Select * from pubs. dbo. t_20
Union all
Select * from northwind. dbo. t_30
Go
-- Insert data
Insert v_t select 1, 'A'
Union all select 2, 'bb'
Union all select 11, 'cc'
Union all select 12, 'dd'
Union all select 21, 'ee'
Union all select 22, 'ff'
-- Update Data
Update v_t set name = name + '_ Update' where right (id, 1) = 1
-- Delete test
Delete from v_t where right (id, 1) = 2
-- Display Results
Select * from v_t
Go
-- Delete test
Drop table northwind. dbo. t_30, pubs. dbo. t_20, tempdb. dbo. t_10
Drop view v_t
/** // * -- Test Result
Id name
---------------------
1 aa _ update
11 cc _ update
21 ee _ update
(The number of affected rows is 3)
= */
8. Tree-based implementation
-- Reference
-- Tree data query example
-- Author: zhujian
-- Sample Data
Create table [tb] ([id] int identity (1, 1), [pid] int, name varchar (20 ))
Insert [tb] select 0, 'China'
Union all select 0, 'America'
Union all select 0, 'Canada'
Union all select 1, 'beijing'
Union all select 1, 'shanghai'
Union all select 1, 'jiangsu'
Union all select 6, 'suzhou'
Union all select 7, 'changshu'
Union all select 6, 'nanjing'
Union all select 6, 'wuxi'
Union all select 2, 'newyork'
Union all select 2, 'san Francisco'
Go
-- Query all sub-accounts of a specified id
Create function f_cid (
@ Id int
) Returns @ re table ([id] int, [level] int)
As
Begin
Declare @ l int
Set @ l = 0
Insert @ re select @ id, @ l
While @ rowcount> 0
Begin
Set @ l = @ l + 1
Insert @ re select a. [id], @ l
From [tb] a, @ re B
Where a. [pid] = B. [id] and B. [level] = L-1
End
/** // * -- If only the most detailed child is displayed (no child is shown below), add the delete
Delete a from @ re
Where exists (
Select 1 from [tb] where [pid] = a. [id])
--*/
Return
End
Go
-- Call (query all sub-accounts)
Select a. *, level = B. [level] from [tb] a, f_cid (2) B where a. [id] = B. [id]
Go
-- Delete test
Drop table [tb]
Drop function f_cid
Go
9. sorting problems
Create table [t] (
[Id] [int] IDENTITY (1, 1) not null,
[GUID] [uniqueidentifier] NULL
) ON [PRIMARY]
GO
The following sentence is executed five times.
Insert t values (newid ())
View execution results
Select * from t
1. First
Select * from t
Order by case id when 4 then 1
When 5 then 2
When 1 then 3
When 2 then 4
When 3 then 5 end
2. Type 2
Select * from t order by (id + 2) % 6
3. Category 3
Select * from t order by charindex (cast (id as varchar), '123 ')
4. Category 4
Select * from t
WHERE id between 0 and 5
Order by charindex (cast (id as varchar), '123 ')
5. Category 5
Select * from t order by case when id> 3 then id-5 else id end
6. Sixth
Select * from t order by id/4 desc, id asc
10. delete a batch of records with one statement
First, the id column is int to identify the class type, and then delete the columns whose ID value is 5, 6, 8, 9, 10, and 11. Here, the cast function cannot be replaced by the convert function, and the conversion type must be varchar, it cannot be char, otherwise it will execute the result you don't want. Here, "," can be the value built by a chkboxlist you get on the page, delete all the following statements.
Besides, it is more efficient than using multiple statements in a loop.
Delete from [Fuji] where charindex (',' + cast ([id] as varchar) + ',' + '5, 6, 8, 9, 10, 11, '+', ')> 0
Another type is
Delete from table1 where id in (1, 2, 3, 4)
11. Obtain the composite string of a column of data in the sub-table
The following function obtains a registered lawyer in 05 years. The only parameter is the name of the firm, and then returns all lawyers whose zhuce field contains the words 05.
Create function fn_Get05LvshiNameBySuo (@ p_suo Nvarchar (50 ))
RETURNS Nvarchar (2000)
AS
BEGIN
DECLARE @ LvshiNames varchar (2000), @ name varchar (50)
Select @ LvshiNames =''
DECLARE lvshi_cursor CURSOR
There are 5 records in the database: 1, 2, 3, 4, and 5. Use an SQL statement to sort the records into 4, 5, 1, 2, and 3. How can this problem be solved? -- Data Operations
SELECT -- retrieve data rows and columns from database tables
INSERT -- add new data rows to the database table
DELETE -- DELETE data rows from a database table
UPDATE -- UPDATE data in the database table
-- Data Definition
Create table -- CREATE a database TABLE
Drop table -- delete a TABLE from a database
Alter table -- modify the database TABLE structure
Create view -- CREATE a VIEW
Drop view -- delete a VIEW from a database
Create index -- CREATE an INDEX for the database table
Drop index -- delete an INDEX from a database
Create procedure -- CREATE a stored PROCEDURE
Drop procedure -- delete a stored PROCEDURE from a database
