SQL笔记

1.通过sp里面的关键字查询sp名称
SELECT obj.Name 存储过程名, sc.TEXT 存储过程内容  
FROM syscomments sc  
INNER JOIN sysobjects obj ON sc.Id = obj.ID  
WHERE obj.Name LIKE 'OTD%' AND sc.TEXT LIKE '%关键字%' 
2.修改栏位名称
sp_rename 'PRD_GET_PARM_VALUE_TASK.TASK_VALUE',PARM_VALUE,'column'
3.修改表名
sp_rename tablename,newtablename
4.修改栏位数据类型
ALTER TABLE student ALTER COLUMN name VARCHAR(200) NOT NULL DEFAULT ''
5.删除栏位
ALTER TABLE student DROP COLUMN nationality;
6.增加字段
ALTER TABLE student Add nationality varchar(20)
7.增加索引
CREATE NONCLUSTERED INDEX AAA ON dbo.Base_Area(F_AreaId,F_ParentId)
CIX_聚集索引名、NCIX_非聚集索引名
8.设置唯一约束
alter table Base_Area add constraint unique1 UNIQUE(F_AreaId,F_ParentId)
9.删除索引
DROP INDEX NCIX_CODE_SN ON PRD_SN_KEY_PARM_RELEVANCE
alter table Sale_OrderList drop constraint IX_Sale_BOM_GMIDandDate
10.修改索引名称
sp_rename 'tabName.old_indName','new_indName','INDEX'
11.varchar可以接收任何类型的数据，计算百分比时可以这样
SELECT PRODUCTION AS '产品',
 PRODUCT AS '实际产出',
 CAST(CAST(PRODUCT AS decimal (20,2)) / CAST (PRODUCTPLAN AS decimal (20,2) ) AS DECIMAL (5,2)) *100  AS '达成率',
 SCRAPPED AS '报废数量',
 CAST(CAST(SCRAPPED AS decimal (20,2)) / CAST (PRODUCT AS decimal (20,2) ) AS DECIMAL (5,2)) *100  AS '报废率'
  FROM @TABLE

CAST(round(convert(float,isnull(@COMPLETE_QTY4,0))/convert(float,ISNULL(@PLAN_QTY4,1))*100,1) as varchar(50))+'%'
12.删除数据库日志
DUMP TRANSACTION 数据库名 WITH NO_LOG
13.
SELECT XXXXX FROM XXXXX GROUP BY  CASE WHEN RIGHT(PROCESS_CODE,2) IN('LH','RH') THEN LEFT(PROCESS_CODE,LEN(PROCESS_CODE)-2) ELSE PROCESS_CODE END
14.开窗函数
SELECT FName, FCITY, FAGE, FSalary,
(
SELECT COUNT(FName) FROM T_Person
WHERE FSALARY<5000
)
FROM T_Person
WHERE FSALARY<5000
开窗函数如下：
SELECT FName, FCITY, FAGE, FSalary, COUNT(FName) OVER()
FROM T_Person
WHERE FSALARY<5000

SELECT * FROM (SELECT T.DATA_ID,T.EQU_NO,T.UNIT_SN,T.UNIT_HISTORY_REC_NO,T.CHILD_UNIT_SN,T.DATA_NAME,T.VALUE_TEXT,T.DATA_DESC,T.RESULT,T.DATA_SEQ,ROW_NUMBER() OVER (PARTITION BY T.DATA_ID ORDER BY CONVERT(INT,T.DATA_SEQ) DESC) AS 'NO' FROM 
(SELECT QH.DATA_ID,QH.EQU_NO,QH.UNIT_SN,QH.UNIT_HISTORY_REC_NO,QH.CHILD_UNIT_SN,QB.DATA_NAME,QB.VALUE_TEXT,QB.DATA_DESC,QB.DATA_SEQ,QB.RESULT FROM QMS_INSPECTION_DATA_HEADER AS 
QH LEFT JOIN QMS_INSPECTION_DATA_BODY AS QB ON QB.DATA_ID=QH.DATA_ID WHERE CHILD_UNIT_STATE='FAIL' AND QB.RESULT='F' OR QB.RESULT='FAIL')T)T2 WHERE T2.NO=1
15.查询所有表的大小
create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100)) 
 
declare @name varchar(100) 
declare cur cursor  for 
    select name from sysobjects where xtype='u' order by name 
open cur 
fetch next from cur into @name 
while @@fetch_status=0 
begin 
    insert into #data 
    exec sp_spaceused   @name 
    print @name 
 
    fetch next from cur into @name 
end 
close cur 
deallocate cur 
 
create table #DataNew(name varchar(100),row int,reserved int,data int,index_size int,unused int) 
 
insert into #dataNew 
select name,convert(int,row) as row,convert(int,replace(reserved,'KB','')) as reserved,convert(int,replace(data,'KB','')) as data, 
convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unused,'KB','')) as unused from #data  
 
select * from #dataNew order by data DESC

16收缩数据库
USE OTDMES;

GO

-- 将数据库设置为简单恢复模式，

ALTER DATABASE OTDMES

SET RECOVERY SIMPLE;

GO

--压缩为100M

DBCC SHRINKFILE (OTDMES_log, 100);

GO

-- 恢复

ALTER DATABASE OTDMES

SET RECOVERY FULL;

GO

17增加索引建议
SELECT TOP 30
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0)
, avg_user_impact
, TableName = statement
, [EqualityUsage] = equality_columns
, [InequalityUsage] = inequality_columns
, [Include Cloumns] = included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
where statement like '%ecology%'
ORDER BY [Total Cost] DESC;

18SQL语句优化
SELECT  c.last_execution_time ,    --最后一次执行时间
        c.execution_count ,    --执行次数
        c.total_logical_reads ,    --总逻辑读（次）
        c.total_logical_writes ,    --总逻辑写（次）
        c.total_elapsed_time ,    --总运行（执行）语句使用的时间（微秒）
        c.last_elapsed_time ,    --最后运行（执行）语句使用的时间（微秒）
        q.[text] ,    --对应的sql语句
        c.total_worker_time / 1000000 total_worker_time_second    --c.total_worker_time 总工作时间（微秒）
FROM    ( SELECT TOP 50
                    qs.*
          FROM      sys.dm_exec_query_stats qs
          ORDER BY  qs.total_worker_time DESC
        ) AS c
        CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY c.last_elapsed_time DESC;

19 
WITH x AS(
SELECT PH.RECID,PH.REC_NO,PH.PROCESS_CODE,PH.UNIT_SN,PH.UNIT_MATERIAL_NO,PH.UNIT_STATE,
PH.UNIT_STATUS,PH.NEXT_PROCESS_CODE,ISNULL(PI.DEFECT_CODE,'') AS 'DEFECT_CODE',
ROW_NUMBER() OVER (PARTITION BY PH.PROCESS_CODE,PH.UNIT_SN ORDER BY PH.RECID DESC) AS 'NO'
FROM dbo.PRD_UNIT_PRD_HISTORY AS PH WITH (NOLOCK) 
LEFT JOIN PRD_UNIT_DEFECT_INFO AS PI WITH(NOLOCK) ON PI.UNIT_HISTORY_ID=PH.RECID
WHERE ISNULL(PH.REMARK,'')<>'Rework' 
)




SELECT RECID,REC_NO,PROCESS_CODE,UNIT_SN,T.UNIT_MATERIAL_NO,UNIT_STATE,UNIT_STATUS,NEXT_PROCESS_CODE,DEFECT_CODE,T.NO FROM x T 
WHERE T.NO>1 AND EXISTS (SELECT b.RECID frpm FROM x a,x b WHERE b.NO=a.NO+1 AND b.UNIT_SN=a.UNIT_SN AND b.PROCESS_CODE=a.PROCESS_CODE
AND b.RECID=T.RECID)


with x as(
select 1 as id, 'n1' as name, 'u1' as muser union all 
select 2 as id, 'n1' as name, 'u2' as muser union all 
select 3 as id, 'n1' as name, 'u2' as muser union all 
select 4 as id, 'n2' as name, 'u3' as muser union all 
select 5 as id, 'n1' as name, 'u4' as muser 
)
select * from x c where not exists(
select b.id from x a, x b
where b.id = a.id-1
and b.name = a.name
and c.id = b.id
)
order by c.id

20.修改自定义表类型的结构
--判断Type是否存在，如果存在，重命名，随后之后才再删除，否则无法直接删除
IF EXISTS (SELECT 1 FROM sys.types t join sys.schemas s on t.schema_id=s.schema_id 
      and t.name='SH_DX_DATA' and s.name='dbo')
 EXEC sys.sp_rename 'dbo.SH_DX_DATA', 'obsoleting_SH_DX_DATA';
GO
 
 
--重建TYPE，比如原来是四个字段，现在想修改为三个字段，或者原来有三个字段想加一个字段变成四个字段
CREATE TYPE [dbo].[SH_DX_DATA] AS TABLE(
    [PRODUCT_NO] [VARCHAR](16) NULL,
    [UNIT_SN] [VARCHAR](400) NULL)
GO
 
--将原来引用将要删除的TYPE全部重建一遍，否则原始存储过程会报错
DECLARE @Name NVARCHAR(500);
DECLARE REF_CURSOR CURSOR FOR
SELECT referencing_schema_name + '.' + referencing_entity_name
FROM sys.dm_sql_referencing_entities('dbo.SH_DX_DATA', 'TYPE');
 OPEN REF_CURSOR;
 FETCH NEXT FROM REF_CURSOR INTO @Name;
 WHILE (@@FETCH_STATUS = 0)
 BEGIN
  EXEC sys.sp_refreshsqlmodule @name = @Name;
  FETCH NEXT FROM REF_CURSOR INTO @Name;
 END;
CLOSE REF_CURSOR;
DEALLOCATE REF_CURSOR;
GO
 
--最后删除原始的被重命名的TableType(被第一步重名的那个)
IF EXISTS (SELECT 1 FROM sys.types t 
   join sys.schemas s on t.schema_id=s.schema_id 
   and t.name='obsoleting_SH_DX_DATA' and s.name='dbo')
 DROP TYPE dbo.obsoleting_SH_DX_DATA
GO
 
--最后执行授权
GRANT EXECUTE ON TYPE::dbo.SH_DX_DATA TO public
GO


SELECT COUNT(1) FROM sysobjects WHERE xtype='U'
SELECT COUNT(1) FROM sysobjects WHERE xtype='V'
SELECT COUNT(1) FROM sysobjects WHERE xtype='P'