SqlServer sql常用查询存在性语句

 

不同数据库的表复制（索引等需要手动复制）

select * into BusinessData_QuotedPrice_Dev.dbo.MoleculePrice from BusinessData_New_Dev_v2.dbo.MoleculePrice

查询系统死锁资源

SELECT  request_session_id AS spid ,
        OBJECT_NAME(resource_associated_entity_id) AS 'table'
FROM    sys.dm_tran_locks
WHERE   resource_type = 'OBJECT';

  关闭死锁占据资源

KILL 76(spid的值)

Sql Server 还原数据库到指定路径语句

restore database 数据库名称 from disk='备份文件路径' with RECOVERY,REPLACE,move '数据库名称' to 
'为 数据库名称.mdf 文件指定的存放路径',move '数据库名称_log' to '为 数据库名称_log.ldf 文件指定的存放路径';

示例：

restore database test from disk='E:\test.bak' with RECOVERY,REPLACE,move 'test' to 
'E:\dbFile\test.mdf',move 'test_log' to 'E:\dbFile\test_log.ldf';

 

Sql Server重置一个表

truncate table 表名

 

Sql Server检测表存在

--Table Data
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChemicalAlias]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
select * into dbo.ChemicalAlias from StorageData.dbo.ChemicalAlias
GO

 Sql Server检测索引存在

--Table Index
if not exists (select * from sysindexes where id=object_id('dbo.ChemicalAlias') and name='index_alias_type')
CREATE NONCLUSTERED INDEX [index_alias_type] ON [dbo].[ChemicalAlias]
(
    [alias] ASC,
    [type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

 Sql Server检测主键存在

--Table Key
if not exists (SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = 'dbo.ChemicalAlias')
ALTER TABLE [dbo].[ChemicalAlias] ADD  CONSTRAINT [PK_ChemicalAlias] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Sql Server检测字段是否存在
      
IF COL_LENGTH('表', '列名') IS NOT NULL    
    PRINT N'存在'    
ELSE    
    PRINT N'不存在'  

Sql Server修改字段排序类型 

Alter table 表名 Alter column 列名  varchar(512) COLLATE  French_CI_AS

Sql Server查询数据存在性语句

Declare @count int
select @count=count(*) from [__EFMigrationsHistory] where [MigrationId]= N'20200424062647_AddColumn_SmilesMol_SearchTask_SearchData'
if @count=0
INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES (N'20200424062647_AddColumn_SmilesMol_SearchTask_SearchData', N'2.2.4-servicing-10062');
GO