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'