U8的业务数据，日常设置等信息基本都存放于数据库，维护好数据库是日常工作中的一重要环节。

 --统计表的记录行数,数据，索引空间等信息

USE [UFSystem]

GO

/****** Object:  StoredProcedure [dbo].[sp_UpdateTableSpaceInfo]    Script Date: 2017-12-22 09:19:21 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET NOCOUNT ON 

GO

ALTER PROCEDURE [dbo].[sp_UpdateTableSpaceInfo]

AS

BEGIN

    --查询是否存在结果存储表

    IF NOT EXISTS (SELECT * FROM sysobjects where id = OBJECT_ID('temp_tableSpaceInfo') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)

    BEGIN

        --不存在则创建

        CREATE TABLE temp_tableSpaceInfo

        (name NVARCHAR(128), 

        rows int, 

        reserved VARCHAR(18),

        data VARCHAR(18),

        index_size VARCHAR(18),

        unused VARCHAR(18))

    END

    --清空数据表

TRUNCATE TABLE temp_tableSpaceInfo

    --定义临时变量在遍历时存储表名称

    DECLARE @tablename VARCHAR(256)

    --使用游标读取数据库内所有表表名

    DECLARE table_list_cursor CURSOR FOR 

    SELECT name FROM sysobjects 

    WHERE OBJECTPROPERTY(id, 'IsTable') = 1 AND (name NOT LIKE '#%' OR name NOT LIKE '##%')  ORDER BY name

    --打开游标

    OPEN table_list_cursor

    --读取第一条数据

    FETCH NEXT FROM table_list_cursor INTO @tablename 

    --遍历查询到的表名

    WHILE @@FETCH_STATUS = 0

    BEGIN

        --检查当前表是否为用户表

        IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, 'IsUserTable') = 1)

        BEGIN

            --当前表则读取其信息插入到表格中

            EXECUTE sp_executesql N'INSERT INTO temp_tableSpaceInfo EXEC sp_spaceused @tbname', N'@tbname varchar(256)', @tbname = @tablename

        END

        --读取下一条数据

        FETCH NEXT FROM table_list_cursor INTO @tablename 

    END

    --释放游标

    CLOSE table_list_cursor

    DEALLOCATE table_list_cursor

END

SET NOCOUNT OFF

--总空间大小

SELECT *,CONVERT(INT,SUBSTRING(reserved,0,CHARINDEX(' ',reserved)))+CONVERT(INT,SUBSTRING(Data,0,CHARINDEX(' ',data)))+CONVERT(INT,SUBSTRING(index_size,0,CHARINDEX(' ',index_size))) AS TOTAL_SIZE_KB 

FROM temp_tableSpaceInfo ORDER BY CONVERT(INT,SUBSTRING(reserved,0,CHARINDEX(' ',reserved)))+CONVERT(INT,SUBSTRING(Data,0,CHARINDEX(' ',data)))+CONVERT(INT,SUBSTRING(index_size,0,CHARINDEX(' ',index_size))) DESC

--按ROWS降序

SELECT * FROM temp_tableSpaceInfo ORDER BY ROWS DESC

----------------------------------------------------------------

----------------------------------------------------------------
 --更换相关字段内容。

USE [UFDATA_888_2014]

GO

/****** Object:  StoredProcedure [dbo].[sp_UpdateTableCheck]    Script Date: 04/27/2017 15:37:46 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[sp_UpdateTableCheck]

AS

BEGIN

IF NOT EXISTS (SELECT * FROM sysobjects where id = OBJECT_ID(N'temp_tablecheck') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)

    BEGIN

        --不存在则创建

        CREATE TABLE temp_tablecheck

        (name NVARCHAR(255), 

         ctype nvarchar(255),

         ilength smallint,

         )

    END

    --定义临时变量在遍历时存储表名称及字段名称

    DECLARE @tablename nVARCHAR(255);

    declare @fieldname nvarchar(255);

    declare @updatesql1 nvarchar(255);

    declare @updatesql2 nvarchar(255);

    declare @searcher1 nvarchar(10);

    declare @searcher2 nvarchar(10);

    declare @instead1 nvarchar(10);

    declare @irecord  int;  

    set @updatesql1='';

    set @updatesql2='';

    set @searcher1='杜比';

    set @searcher2='DOLBY';

    set @instead1='音效-B';

    set @irecord=1;

    --使用游标读取数据库内所有有记录的表名

    DECLARE table_list_cursor CURSOR FOR 

select name from temp_tablespaceinfo where rows>0 order by rows desc 

--打开游标

    OPEN table_list_cursor

    --读取第一条数据

    FETCH NEXT FROM table_list_cursor INTO @tablename 

    --遍历查询到的表名

    WHILE @@FETCH_STATUS = 0

BEGIN

--清空数据表

DELETE FROM temp_tablecheck

Insert into temp_tablecheck 

SELECT syscolumns.name,systypes.name as ctype,syscolumns.length as ilength 

FROM syscolumns, systypes   

WHERE syscolumns.xusertype = systypes.xusertype  AND syscolumns.id = object_id(@tablename) and 

(systypes.name='nvarchar'  or systypes.name='varchar' or systypes.name='nchar' or systypes.name='char' or systypes.name='text')

   and syscolumns.length>3

   print '★★★★★★★★★';

   print @irecord;

   print @tablename; 

--使用游标读取数据表内所有字段记录

DECLARE field_list_cursor CURSOR FOR 

select name from temp_tablecheck

--打开游标

OPEN field_list_cursor

--读取第一条数据

FETCH NEXT FROM field_list_cursor INTO @fieldname 

WHILE @@FETCH_STATUS = 0

BEGIN

print '-----------------';

print @fieldname;

set @updatesql1='update ['+@tablename+'] set '+@fieldname+'=REPLACE(replace('+@fieldname+

','' '',''''),'''+

+@searcher1+''','''+@instead1+''') where '+@fieldname+' like '''+

'%'+@searcher1+'%'''

print @updatesql1;

exec(@updatesql1);

set @updatesql2='update ['+@tablename+'] set '+@fieldname+'=REPLACE(replace('+@fieldname+

','' '',''''),'''+

+@searcher2+''','''+@instead1+''') where '+@fieldname+' like '''+

'%'+@searcher2+'%'''

print @updatesql2;

exec(@updatesql2);

--读取下一条数据

FETCH NEXT FROM field_list_cursor INTO @fieldname 

END

--释放游标

CLOSE field_list_cursor

DEALLOCATE field_list_cursor

   --读取下一条数据

        FETCH NEXT FROM table_list_cursor INTO @tablename  

   set @irecord=@irecord+1;

    END 

--释放游标

    CLOSE table_list_cursor

    DEALLOCATE table_list_cursor

END

----------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------
--重建索引，更新统计信息

USE [UFSystem]

GO

/****** Object:  StoredProcedure [dbo].[sp_UpdateTableIndex]    Script Date: 2017-12-22 14:30:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[sp_UpdateTableIndex]

AS

BEGIN

    --定义临时变量在遍历时存储表名称

    DECLARE @tablename VARCHAR(256)

    --使用游标读取数据库内所有表表名

    DECLARE table_list_cursor CURSOR FOR 

    SELECT name FROM sysobjects 

    WHERE OBJECTPROPERTY(id, 'IsTable') = 1 AND (name NOT LIKE '#%' OR name NOT LIKE '##%')  ORDER BY name

    --打开游标

    OPEN table_list_cursor

    --读取第一条数据

    FETCH NEXT FROM table_list_cursor INTO @tablename 

    --遍历查询到的表名

    WHILE @@FETCH_STATUS = 0

    BEGIN

        --检查当前表是否为用户表

        IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(@tablename) AND OBJECTPROPERTY(id, 'IsUserTable') = 1)

        BEGIN

             --重建索引

    PRINT @tablename

            EXEC('ALTER INDEX ALL ON ['+@tablename+'] REBUILD')

        END

        --读取下一条数据

        FETCH NEXT FROM table_list_cursor INTO @tablename 

    END

    --释放游标

    CLOSE table_list_cursor

    DEALLOCATE table_list_cursor

END

--更新统计信息

EXEC sys.sp_updatestats

GO 

----------------------------------------------------

----------------------------------------------------

DBCC DROPCLEANBUFFERS:从缓冲池中删除所有缓存，清除缓冲区

SELECT (case when a.colorder=1 then d.name else null end) 表名,  

a.colorder 字段序号,a.name 字段名,

(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识, 

(case when (SELECT count(*) FROM sysobjects  

WHERE (name in (SELECT name FROM sysindexes  

WHERE (id = a.id) AND (indid in  

(SELECT indid FROM sysindexkeys  

WHERE (id = a.id) AND (colid in  

(SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name)))))))  

AND (xtype = 'PK'))>0 then '√' else '' end) 主键,b.name 类型,a.length 占用字节数,  

COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,  

isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,(case when a.isnullable=1 then '√'else '' end) 允许空,  

isnull(e.text,'') 默认值,isnull(g.[value], ' ') AS [说明]

INTO #TEMP_TABLE FROM  syscolumns a 

left join systypes b on a.xtype=b.xusertype  

inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' 

left join syscomments e on a.cdefault=e.id  

left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id

left join sys.extended_properties f on d.id=f.class and f.minor_id=0

where b.name is not null

--WHERE d.name='要查询的表' --如果只查询指定表,加上此条件

order by a.id,a.colorder

SELECT * FROM #TEMP_TABLE WHERE 表名 IS NOT NULL ORDER BY 表名

----------------------------------------------

字段增加约束

---------------------------------------------

USE [UFDATA_991_2014]

GO

ALTER TABLE [dbo].[Inventory]  WITH CHECK ADD  CONSTRAINT [CHK_CINVSTD] CHECK  ([CINVSTD] NOT LIKE '%DOLBY%' AND [CINVSTD] NOT LIKE '%Alibaba%' )

GO

ALTER TABLE [dbo].[Inventory] CHECK CONSTRAINT [CHK_CINVSTD]

GO

---------------------------------------

占用过高语句 实例中前10个逻辑读写占用高的SQL语句

---------------------------------------

USE master

GO

select top 10

(total_logical_reads/execution_count) as [平均逻辑读取次数],

(total_logical_writes/execution_count) as [平均逻辑写入次数],

(total_physical_reads/execution_count) as [平均对象读取次数],

 Execution_count [运行次数], 

substring(qt.text,r.statement_start_offset/2+1, 

(case when r.statement_end_offset = -1 

then datalength(qt.text) 

else r.statement_end_offset end - r.statement_start_offset)/2+1) [运行语法],getdate() [查询时间]

from sys.dm_exec_query_stats   r 

cross apply sys.dm_exec_sql_text(r.sql_handle) qt 

order by 

 (total_logical_reads + total_logical_writes) Desc

----------------------------------------------------------------------
--实例中前50个CPU消耗最高的SQL执行语句（默认单位：微秒）

use master

go

SELECT TOP 50

SUBSTRING(qt.text,(qs.statement_start_offset/2)+1,

((CASE qs.statement_end_offset

WHEN -1 THEN DATALENGTH(qt.text)

ELSE qs.statement_end_offset

END -qs.statement_start_offset)/2)+1) execution_statement

,datebase_name=db.name

,qs.execution_count 

,qs.total_logical_reads 

,logical_reads_per_run=CAST(qs.total_logical_reads*1.0/qs.execution_count AS decimal(12,2))

,qs.last_logical_reads 

,qs.total_logical_writes

,logical_writes_per_run=CAST(qs.total_logical_writes*1.0/qs.execution_count AS decimal(12,2))

,qs.last_logical_writes 

,qs.total_worker_time  

,worker_time_per_run=CAST(qs.total_worker_time*1.0/qs.execution_count AS decimal(12,2))

,qs.last_worker_time 

,total_elapsed_time_in_s=qs.total_elapsed_time*1.0/1000000

,last_elapsed_time_in_s=qs.last_elapsed_time*1.0/1000000

,qs.last_execution_time

,qp.query_plan 

FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

INNER JOIN sys.databases as db on qp.dbid=db.database_id 

--WHERE qs.execution_count>=100

--ORDER BY qs.total_logical_reads /qs.execution_count desc--logical reads per run

--ORDER BY qs.total_logical_writes/qs.exectution_count desc--logical writes per run

--ORDER BY qs.total_worker_time/qs.execution_count desc--CPU time per run

--ORDER BY qs.total_logical_reads DESC--logical reads

--ORDER BY qs.total_logical_writeDESC--logical writes

ORDER BY qs.total_worker_time DESC--CPU time