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'
