sql 常用语句备份

新增字段，默认其他字段计算

ALTER TABLE 表名 add 字段名 as 字段名1+字段名2

SQL查看变量的数据类型

DECLARE @Sum int
--SET @Sum = 0
SELECT @Sum, CAST(SQL_VARIANT_PROPERTY(@Sum, 'BaseType') AS VARCHAR(50))

查询临时表是否存在

if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..#CC') and type='U')

查询某个字段是否存在

IF COL_LENGTH('TBHotelPolicyControl','Flag') IS NULL

查询某个字段在哪张表

select sys.objects.name,* from sys.columns
 inner join sys.objects on sys.columns.object_id = sys.objects.object_id
where sys.columns.name = '字段名'

排序规则，可以在查询，建表时制

SELECT TBCITY.code collate Chinese_PRC_BIN FROM TBCITY

collate Chinese_PRC_BIN，指定按某种方式排序

关闭自动增长

SET IDENTITY_INSERT TableName OFF

游标

create table #A
(
Number varchar(50)
,Name varchar(50)
)
insert into #A
select '123456','Name1'
union
select '123456','Name2'
create table #B
(
Number varchar(50)
,Name varchar(50)
)

declare @Number varchar(50)
declare @Name varchar(50)
DECLARE myCursor CURSOR
FOR (SELECT * FROM #A)
OPEN myCursor
FETCH NEXT FROM myCursor into @Number,@Name
WHILE @@FETCH_STATUS = 0
BEGIN
insert into #B values(@Number,@Name)
FETCH NEXT FROM myCursor into @Number,@Name
END
CLOSE myCursor
DEALLOCATE myCursor

select * from #B

--索引

CREATE NONCLUSTERED INDEX IX_HR_EXP_CLAIM_HEAD_tx_date  --创建一个非聚集索引 ON HR_EXP_CLAIM_HEAD(tx_date)       --为TEST表的TNAME字段创建索引 WITH FILLFACTOR = 50         --填充因子为50% GO

DBCC DBREINDEX (HR_EXP_CLAIM_HEAD,IX_HR_EXP_CLAIM_HEAD_tx_date) UPDATE STATISTICS HR_EXP_CLAIM_HEAD

-- 删除相同留一条

select comp_code,region,nature,exp_code from TBEXPENSES_ALLOCATION
 where comp_code='CNABB'
 group by comp_code,region,nature,exp_code having count(*) > 1

================= 清理Log===================

USE [CSFramework3.Normal.jhzls]

GO

SELECT file_id, name FROM sys.database_files;

GO

--简单模式
USE [master]
GO
ALTER DATABASE [CSFramework3.Normal.jhzls] SET RECOVERY SIMPLE WITH NO_WAIT
GO
ALTER DATABASE [CSFramework3.Normal.jhzls] SET RECOVERY SIMPLE
GO
USE [CSFramework3.Normal.jhzls]
GO
DBCC SHRINKFILE (N'CSFramework3_Normal_Log' , 11, TRUNCATEONLY)

GO

--还原为完全模式
USE [master]
GO
ALTER DATABASE [CSFramework3.Normal.jhzls] SET RECOVERY FULL WITH NO_WAIT
GO
ALTER DATABASE [CSFramework3.Normal.jhzls] SET RECOVERY FULL
GO

-- 察看占用进程

exec sp_who

kill 123 

-- 查询所有表记录数

select object_name(i.id) TableName

        ,rows as RowCount

  from sysindexes i

 inner join sysobjects o on (o.id=i.id and xtype='U')

 order by RowCount desc