Some T-SQL tips

Some T-SQL tips

Some T-SQL tips

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 can I use t-SQL to obtain several elements of this 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 a different role 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", and "06" from ""

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, "5, 6, 8, 9, 10, 11" 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?