sqlserver 常用语法
sqlserver查找 table, view, column
select * from information_schema.tables where table_schema='bk' select * from information_schema.views where table_schema='bk' select * from information_schema.columns where column_name like '%name' -- 查询包含某个列名的表 select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME like '%ColumnName%' -- 查询包含某个基础表的视图 select * from INFORMATION_SCHEMA.VIEWS where VIEW_DEFINITION like '%TableName%'
sqlserver删除语法
-- 1. drop table IF EXISTS (SELECT 1 FROM sysobjects WHERE id = object_id('__TableName__') AND type = 'U' ) DROP TABLE __TableName__; -- 2. drop view if exists (select * from dbo.sysobjects where id = object_id(N'__ViewName__') and objectproperty(id, N'isview') = 1) drop view __ViewName__; -- 3. drop proc IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'__ProcName__') AND type in (N'P', N'PC')) DROP PROCEDURE __ProcName__; IF OBJECT_ID('__ProcName__','P') IS NOT NULL DROP PROC __ProcName__ -- 4. drop columns IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='dbo' AND TABLE_NAME = '__TableName__' AND COLUMN_NAME = '__ColumnName__') alter table __TableName__ DROP COLUMN __ColumnName__; IF EXISTS (SELECT * FROM syscolumns WHERE id=object_id('__TableName__') AND name='__ColumnName__') EXEC('ALTER TABLE __TableName__ DROP COLUMN __ColumnName__') -- 5. drop constraints DECLARE @ConstraintName nvarchar(200) SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID('__TableName__') AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = N'__ColumnName__' AND object_id = OBJECT_ID(N'__TableName__')) IF @ConstraintName IS NOT NULL EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName) -- 6. drop PK or FK IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '__TableName__' AND COLUMN_NAME = '__ColumnName__') BEGIN SELECT @ConstraintName = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = '__TableName__' AND COLUMN_NAME = '__ColumnName__' EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName) END
计算每批次的id集合
with ws_ids as( SELECT ROW_NUMBER() OVER(ORDER BY w.id) / 10 batch_id, w.id FROM dbo.[webservice] w WHERE validationStatus = 'INIT' ) SELECT batch_id, id = stuff( SELECT ',' + cast(id AS VARCHAR(20)) FROM ws_ids t WHERE t.batch_id = a.batch_id FOR XML path(''), 1, 1, '') FROM ws_ids a
批量更新（多表连接，使用临时表）
IF EXISTS (SELECT 1 FROM sysobjects WHERE id = object_id('dbo.month_sale') AND type = 'U') DROP TABLE dbo.month_sale; GO CREATE TABLE dbo.month_sale ( seller_name varchar(50) not null, month_amount decimal(24, 8) not null, PRIMARY KEY(seller_name, amount) ) IF OBJECT_ID('[dbo].[calc_month_sale]','P') IS NOT NULL DROP PROC [dbo].[calc_month_sale]; GO CREATE PROCEDURE [dbo].[calc_month_sale] @month int AS BEGIN SET NOCOUNT ON TRUNCATE TABLE dbo.month_sale CREATE TABLE #seller_month_amount( seller_id int identity(1,1) not null, month_amount datetime not null, PRIMARY KEY(seller_id, month_amount) ) INSERT #seller_month_amount(seller_id, month_amount) SELECT seller_id, sum(amount) FROM [dbo].[order] WHERE month(ordered_date)=@month GROUP BY seller_id INSERT month_sale(seller_name, month_amount) SELECT s.seller_name, sma.month_amount FROM [dbo].[seller] s INNER JOIN #seller_month_amount sma ON s.seller_id = sma.seller_id UPDATE sh SET month_amount = ms.month_amount FROM dbo.sale_history sh INNER JOIN [dbo].[month_sale] ms ON s.seller_id = sma.seller_id END ; exec [dbo].[calc_month_sale]
批量插入
set rowcount 10000000 declare @looper int = 10000000 declare @current_time datetime while @looper = 10000000 begin select @current_time = getdate() insert into table1 select * from table2 select @looper = @@rowcount insert into batch_log values ('table1', @looper, @current_time, getdate()) end
查询replicated tables
-- 1. find publisher and subscriber SELECT DISTINCT srv.srvname publication_server , a.publisher_db , p.publication + '.' + a.article publication_object , ss.srvname subscriber_server , s.subscriber_db , a.destination_object --, da.name AS distribution_agent_job_name FROM [distribution].[dbo].MSArticles a JOIN [distribution].[dbo].MSpublications p ON a.publication_id = p.publication_id JOIN [distribution].[dbo].MSsubscriptions s ON p.publication_id = s.publication_id JOIN master..sysservers ss ON s.subscriber_id = ss.srvid JOIN master..sysservers srv ON srv.srvid = p.publisher_id --JOIN [distribution].[dbo].MSdistribution_agents da ON da.publisher_id = p.publisher_id -- AND da.subscriber_id = s.subscriber_id ORDER BY 1,2,3 -- 2. find replicated tables SELECT P.[publication] AS [Publication Name] ,A.[publisher_db] AS [Database Name] ,A.[article] AS [Article Name] ,A.[source_owner] AS [Schema] ,A.[source_object] AS [Object] FROM [distribution].[dbo].[MSarticles] AS A INNER JOIN [distribution].[dbo].[MSpublications] AS P ON (A.[publication_id] = P.[publication_id]) ORDER BY P.[publication], A.[article]; -- 3. Publisher select t.name from sys.tables t where is_published = 1 -- Subscriber select t.name from [SERVERNAME].[REPLICATED_DATABASE].sys.tables t where is_ms_shipped = 0;
查找所有索引
select s.name as schemaName, t.name as tableName, i.name as indexName from sys.tables t inner join sys.schemas s on t.schema_id = s.schema_id inner join sys.indexes i on i.object_id = t.object_id where i.index_id > 0 and i.type in (1, 2) and i.is_primary_key = 0 and i.is_unique_constraint = 0
查看索引详情
SELECT schema_name(schema_id) as SchemaName, OBJECT_NAME(si.object_id) as TableName, si.name as IndexName, (CASE is_primary_key WHEN 1 THEN 'PK' ELSE 'index' END) as indexType, (CASE is_unique WHEN 1 THEN '1' ELSE '0' END)+' '+ (CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' '+ -- B=basic, C=Clustered, X=XML (CASE INDEXKEY_PROPERTY(si.object_id,index_id,1,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ (CASE INDEXKEY_PROPERTY(si.object_id,index_id,2,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ (CASE INDEXKEY_PROPERTY(si.object_id,index_id,3,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ (CASE INDEXKEY_PROPERTY(si.object_id,index_id,4,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ (CASE INDEXKEY_PROPERTY(si.object_id,index_id,5,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ (CASE INDEXKEY_PROPERTY(si.object_id,index_id,6,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ (CASE INDEXKEY_PROPERTY(si.object_id,index_id,7,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ (CASE INDEXKEY_PROPERTY(si.object_id,index_id,8,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END)+ '' as 'Type', INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,1) as Key1, INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,2) as Key2, INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,3) as Key3, INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,4) as Key4, INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,5) as Key5, INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,6) as Key6, INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,7) as Key7, INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,8) as Key8 FROM sys.indexes as si LEFT JOIN sys.objects as so on so.object_id=si.object_id WHERE index_id>0 -- omit the default heap and OBJECTPROPERTY(si.object_id,'IsMsShipped')=0 -- omit system tables --and not (schema_name(schema_id)='dbo' and OBJECT_NAME(si.object_id)='sysdiagrams') -- omit sysdiagrams and schema_name(schema_id)='fdp_cvs' -- and OBJECT_NAME(si.object_id) ='fdp_cvs' ORDER BY SchemaName,TableName,IndexName
sqlserver日期函数
DATEADD(datepart,number,date) DATEDIFF(datepart,startdate,enddate) DATEPART(datepart,date) select getdate() -- 当前日期 8/15/2017 3:21:45 AM select dateadd(m, 0, 0) -- 时间起点 1/1/1900 12:00:00 AM select dateadd(m, datediff(m,0,getdate())-2, 0) -- 当前季度的第一天 6/1/2017 12:00:00 AM select dateadd(m, datediff(m,0,getdate())-11, 0)-- 一年前当月的第一天 9/1/2016 12:00:00 AM select dateadd(d, 1, dateadd(q,-1,getdate())) -- 一季度前的起始日期 5/16/2017 3:21:45 AM select dateadd(d, 2, dateadd(y,-1,getdate())) -- 一年前的起始日期 8/16/2017 3:21:45 AM select CONVERT(datetime,CONVERT(char(8),getdate(),120)+'1') -- 该月第一天 8/1/2017 12:00:00 AM select CONVERT(char(5), getdate(),120)+'1-1' -- 年的第一天 select CONVERT(char(5), getdate(),120)+'12-31' -- 年的最后一天 select 年份=year(getdate()),月份=month(getdate()) select 年份＝datepart(year,getdate()),月份=datepart(month,getdate())
创建数据库快照
NAME: snapshot文件的逻辑名称，FILENAME: snapshot文件的存放位置 CREATE DATABASE AdventureWorks_dbss1800 ON ( NAME = AdventureWorks_Data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Data\AdventureWorks_data_1800.ss' ) AS SNAPSHOT OF AdventureWorks; GO
