SQL Server common SQL Summary

SQL Server common SQL Summary

Order by NAME collate Chinese_PRC_Stroke_CS_AS_KS_WS

/* Sqlserver grouping cannot use text, ntext, or image fields as the grouping basis */

-- Use index for mandatory query:

Select id from table_name with (index name) where num = @ num

-- Full-text search (name like '% abc %') (substring (cal_name, 1, 3) = 'abc ')

Select id from t where charindex ('abc', cal_name)> 0

-- View object definitions

Sp_helptext name

-- Arrange strokes of Chinese characters from less to more

Select * from table_name order by cal_name collate chinese_prc_stroke_ci_as

-- Any first 10 records

Select top 10 * from table_name order by newid ()

-- An into statement is required when the sequence number is added.

Select identity (int, 1, 1) id, * into # table_name from table_name

-- 10 ~ 20 rows of data

Select top 10 * from (select top 20 * from # table_name order by id asc) as new_tab_name order by id desc

-- Randomly retrieve 10 data records

Select top 10 * from table_name order by newid ()

-- Repeat the specified number of times.

Select replicate ('k', 5) -- kkkkkk

-- Count the number of Chinese Characters

Select datalength ('kk China 123 ')-len ('kk China 123 ')

Select nullif ('kk ', 'kk') -- equal to null, otherwise take the first

Select isnull (null, 'kk ') -- if the first value is not null, the first value is used. Otherwise, the second value is used.

Select coalesce (null, null, 'kk ', 'China') -- returns the first non-null value.

-- Integer

Select CEILING (12.7) -- [13]; returns the smallest integer greater than 12.7.

Select CEILING (12.2) -- [13]; returns the smallest integer greater than 12.2.

Select FLOOR (12.7) -- [12]; returns the maximum integer smaller than 12.7

Select FLOOR (12.2) -- [12]; returns the maximum integer smaller than 12.2

Select round (12.77, 0) -- [13.00]; rounding, 0 decimal places

Select round (12.24, 1) -- [12.20]; rounding, 1 decimal

-- Replace by location

Select STUFF ('abcdefg', 2, 0, '-') -- [A-BCDEFG]; second position, take the character length to 0, replace-

Select STUFF ('abcdefg', 2, 1, 'B') -- [ABCDEFG]; second position, take the character length as 1, replace with B

Select STUFF ('abcdefg', 2, 2, '*') -- [A * DEFG]; second position. Replace the character length with 2 and *

-- Replace with the same character

Select REPLACE ('abcdefg', 'B', 'B') -- [ABCDEFG]; REPLACE all B with B

Select REPLACE ('abcdefg-bc', 'bc', '*') -- [A * DEFG-*]; REPLACE all Bc values with one *, which is case insensitive.

-- Determine whether a character exists

Select CHARINDEX ('20140901', '20160901 ')

Select CHARINDEX ('1', '123') -- [0]; judge whether 1 exists

Select CHARINDEX ('1', '123') -- [1]; 1 position

Select CHARINDEX ('1', '123', 8) -- [10]; query from the eighth character, where 1 appears in the string

-- Starting position, supporting matching expressions

Select patindex ('20140901', '20160901') -- [0];

Select patindex ('20140901', '20160901') -- [0];

Select patindex ('% 100', '123') -- [4];

Select patindex ('20140901', '20160901') -- [1];

Select patindex ('_ 3%', '123') -- [1];

Select patindex ('% [js] %', 'hsdjgjsrgsdgfjt ') -- returns the position where the first character appears in j or s.

Select patindex ('% [^ js] %', 'ssjjgjsrgsdgfjt ') -- returns the position where the first character outside j and s appears.

Print 'start execution output'

Go

Waitfor delay '00: 00: 05 '-- executed in 5 seconds

Print 'delayed execution output'

Go

Waitfor time '12: 00: 00' -- execute at 12

Print 'timed execution output'

Go

-- Remote connection

Select * from openrowset ('sqloledb', 'servername'; 'username'; 'Password', dbname. dbo. tablename)

Select * from opendatasource ('sqloledb', 'Data source = ip (or servername); user id = username; password = password '). Dbname. dbo. tablename

-- Add one month to the current date

Select convert (varchar (10), dateadd (m, 1, getdate (), 120)

Select dateadd (m, 1, getdate ())

-- Get the last day of the current year and month

Select dateadd (d,-1, convert (datetime, convert (varchar (7), dateadd (m, 1, getdate (), 120) + '-01 '))

-- Obtain the first day of the current year and month

Select convert (datetime, convert (varchar (7), getdate (), 120) + '-01', 120)

-- Get the first month of this year

Select convert (varchar (4), getdate (), 120) + '-01'

-- Obtain the previous month of the current month

Select convert (varchar (7), dateadd (m,-1, getdate (), 120)

-- The first day of the current quarter

Select dateadd (quarter, datediff (quarter, 0, getdate (), 0)

-- The last day of this year

Select dateadd (MS,-3, dateadd (yy, datediff (yy, 0, getdate () + 1, 0 ))

-- Determines whether a leap year is used

Select case day (dateadd (mm, 2, dateadd (MS,-3, dateadd (yy, datediff (yy, 0, getdate (), 0 )))) when 28 then 'Year' else' else 'end'

Select case datediff (day, datename (year, getdate () + '-02-01', dateadd (mm, 1, datename (year, getdate ()) + '-02-01') when 28 then 'Year-on-year else' leap year' end

/* Check whether an object or table exists */

-- View all other table-related objects

Select a. * from sysobjects a, syscomments B where a. id = B. id and B. text like '% tb %'

-- View all tables of the current database

Select name from sysobjects where xtype = 'U' and status> = 0

Select * from information_schema.tables where table_type = 'base table'

-- View all data columns of a specified table

Select name from syscolumns where id = object_id ('tablename ')

Select column_name from information_schema.columns where table_name = 'tablename'

-- View all views of the current database

Select * from information_schema.views -- (defined)

Select * from dbo. sysobjects where objectproperty (id, n 'isview') = 1

Select * from dbo. sysobjects where type = 'V'

-- Determine whether the database exists

If exists (select 1 from master. dbo. sysdatabases where name = n 'ghhg ')

Select * from mastersysdatabases -- Database

-- Determine whether a temporary table exists

If object_id (n 'tempdb [# temp_table] ') is not null

If exists (select * from tempdb. dbo. sysobjects where id = object_id (n' [tempdb]. [dbo]. [# temp_table] ')

-- Determine whether a job exists

If exists (select job_id from msdb. dbo. sysjobs_view where name = 'jobname ')

Order by NAME collate Chinese_PRC_Stroke_CS_AS_KS_WS

/* Sqlserver grouping cannot use text, ntext, or image fields as the grouping basis */

-- Use index for mandatory query:

Select id from table_name with (index name) where num = @ num

-- Full-text search (name like '% abc %') (substring (cal_name, 1, 3) = 'abc ')

Select id from t where charindex ('abc', cal_name)> 0

-- View object definitions

Sp_helptext name

-- Arrange strokes of Chinese characters from less to more

Select * from table_name order by cal_name collate chinese_prc_stroke_ci_as

-- Any first 10 records

Select top 10 * from table_name order by newid ()

-- An into statement is required when the sequence number is added.

Select identity (int, 1, 1) id, * into # table_name from table_name

-- 10 ~ 20 rows of data

Select top 10 * from (select top 20 * from # table_name order by id asc) as new_tab_name order by id desc

-- Randomly retrieve 10 data records

Select top 10 * from table_name order by newid ()

-- Repeat the specified number of times.

Select replicate ('k', 5) -- kkkkkk

-- Count the number of Chinese Characters

Select datalength ('kk China 123 ')-len ('kk China 123 ')

Select nullif ('kk ', 'kk') -- equal to null, otherwise take the first

Select isnull (null, 'kk ') -- if the first value is not null, the first value is used. Otherwise, the second value is used.

Select coalesce (null, null, 'kk ', 'China') -- returns the first non-null value.

-- Integer

Select CEILING (12.7) -- [13]; returns the smallest integer greater than 12.7.

Select CEILING (12.2) -- [13]; returns the smallest integer greater than 12.2.

Select FLOOR (12.7) -- [12]; returns the maximum integer smaller than 12.7

Select FLOOR (12.2) -- [12]; returns the maximum integer smaller than 12.2

Select round (12.77, 0) -- [13.00]; rounding, 0 decimal places

Select round (12.24, 1) -- [12.20]; rounding, 1 decimal

-- Replace by location

Select STUFF ('abcdefg', 2, 0, '-') -- [A-BCDEFG]; second position, take the character length to 0, replace-

Select STUFF ('abcdefg', 2, 1, 'B') -- [ABCDEFG]; second position, take the character length as 1, replace with B

Select STUFF ('abcdefg', 2, 2, '*') -- [A * DEFG]; second position. Replace the character length with 2 and *

-- Replace with the same character

Select REPLACE ('abcdefg', 'B', 'B') -- [ABCDEFG]; REPLACE all B with B

Select REPLACE ('abcdefg-bc', 'bc', '*') -- [A * DEFG-*]; REPLACE all Bc values with one *, which is case insensitive.

-- Determine whether a character exists

Select CHARINDEX ('20140901', '20160901 ')

Select CHARINDEX ('1', '123') -- [0]; judge whether 1 exists

Select CHARINDEX ('1', '123') -- [1]; 1 position

Select CHARINDEX ('1', '123', 8) -- [10]; query from the eighth character, where 1 appears in the string

-- Starting position, supporting matching expressions

Select patindex ('20140901', '20160901') -- [0];

Select patindex ('20140901', '20160901') -- [0];

Select patindex ('% 100', '123') -- [4];

Select patindex ('20140901', '20160901') -- [1];

Select patindex ('_ 3%', '123') -- [1];

Select patindex ('% [js] %', 'hsdjgjsrgsdgfjt ') -- returns the position where the first character appears in j or s.

Select patindex ('% [^ js] %', 'ssjjgjsrgsdgfjt ') -- returns the position where the first character outside j and s appears.

Print 'start execution output'

Go

Waitfor delay '00: 00: 05 '-- executed in 5 seconds

Print 'delayed execution output'

Go

Waitfor time '12: 00: 00' -- execute at 12

Print 'timed execution output'

Go

-- Remote connection

Select * from openrowset ('sqloledb', 'servername'; 'username'; 'Password', dbname. dbo. tablename)

Select * from opendatasource ('sqloledb', 'Data source = ip (or servername); user id = username; password = password '). Dbname. dbo. tablename

-- Add one month to the current date

Select convert (varchar (10), dateadd (m, 1, getdate (), 120)

Select dateadd (m, 1, getdate ())

-- Get the last day of the current year and month

Select dateadd (d,-1, convert (datetime, convert (varchar (7), dateadd (m, 1, getdate (), 120) + '-01 '))

-- Obtain the first day of the current year and month

Select convert (datetime, convert (varchar (7), getdate (), 120) + '-01', 120)

-- Get the first month of this year

Select convert (varchar (4), getdate (), 120) + '-01'

-- Obtain the previous month of the current month

Select convert (varchar (7), dateadd (m,-1, getdate (), 120)

-- The first day of the current quarter

Select dateadd (quarter, datediff (quarter, 0, getdate (), 0)

-- The last day of this year

Select dateadd (MS,-3, dateadd (yy, datediff (yy, 0, getdate () + 1, 0 ))

-- Determines whether a leap year is used

Select case day (dateadd (mm, 2, dateadd (MS,-3, dateadd (yy, datediff (yy, 0, getdate (), 0 )))) when 28 then 'Year' else' else 'end'

Select case datediff (day, datename (year, getdate () + '-02-01', dateadd (mm, 1, datename (year, getdate ()) + '-02-01') when 28 then 'Year-on-year else' leap year' end

/* Check whether an object or table exists */

-- View all other table-related objects

Select a. * from sysobjects a, syscomments B where a. id = B. id and B. text like '% tb %'

-- View all tables of the current database

Select name from sysobjects where xtype = 'U' and status> = 0

Select * from information_schema.tables where table_type = 'base table'

-- View all data columns of a specified table

Select name from syscolumns where id = object_id ('tablename ')

Select column_name from information_schema.columns where table_name = 'tablename'

-- View all views of the current database

Select * from information_schema.views -- (defined)

Select * from dbo. sysobjects where objectproperty (id, n 'isview') = 1

Select * from dbo. sysobjects where type = 'V'

-- Determine whether the database exists

If exists (select 1 from master. dbo. sysdatabases where name = n 'ghhg ')

Select * from mastersysdatabases -- Database

-- Determine whether a temporary table exists

If object_id (n 'tempdb [# temp_table] ') is not null

If exists (select * from tempdb. dbo. sysobjects where id = object_id (n' [tempdb]. [dbo]. [# temp_table] ')

-- Determine whether a job exists

If exists (select job_id from msdb. dbo. sysjobs_view where name = 'jobname ')

[SQL] view plaincopyprint?

/*

Dbcc freeproccache -- clear execution plan Cache

Dbcc dropcleanbuffers with NO_INFOMSGS -- clear the buffer

Set statistics profile on

Set statistics io on

Set statistics time on

Set statistics profile off

Set statistics io off

Set statistics time off

*/

/*

Dbcc freeproccache -- clear execution plan Cache

Dbcc dropcleanbuffers with NO_INFOMSGS -- clear the buffer

Set statistics profile on

Set statistics io on

Set statistics time on

Set statistics profile off

Set statistics io off

Set statistics time off

*/

[SQL] view plaincopyprint?

-- Database name query with known table names

Declare @ Table table (DB sysname, TabName sysname)

Insert @ Table

Exec sp_msforeachdb 'select ''? ''As DB, Name as table Name from [?] Sysobjects where type = ''u'' and Name = ''fjda '''

Select * from @ Table

-- Database name query with known table names

Declare @ Table table (DB sysname, TabName sysname)

Insert @ Table

Exec sp_msforeachdb 'select ''? ''As DB, Name as table Name from [?] Sysobjects where type = ''u'' and Name = ''fjda '''

Select * from @ Table