sqlserver的日常检查脚本归结

核心提示：CASE CONVERT(varchar(10),DATABASEPROPERTYEX([Name], 'IsAutoClose'))WHEN 0 THEN '-'WHEN 1 THEN 'Yes'END AS AutoClose,CASE CONVERT(varcha

CASE CONVERT(varchar(10),DATABASEPROPERTYEX([Name], 'IsAutoClose'))

WHEN 0 THEN '-'

WHEN 1 THEN 'Yes'

END  AS AutoClose,

CASE CONVERT(varchar(10),DATABASEPROPERTYEX([Name], 'IsAutoShrink'))

WHEN 0 THEN '-'

WHEN 1 THEN 'Yes'

END AS AutoShrink

FROM master.dbo.sysdatabases) q3

ON q1.DBID = q3.dbid

ORDER BY DatabaseName 

print '*********************************'

print '                             '

print '                             '

print '                             '

print '----------------------------'

print '3.查看每个数据库具体结构信息'

print'----------------------------'

print '*********************************'

print '                         '

use master

go

declare @databasename varchar(50)

declare cur01 cursor for

select name from sys.databases

open cur01

fetch next from cur01 into @databasename

while @@fetch_status=0

begin

begin

select 'databasename:  '+ @databasename

print '                     '

exec sp_helpdb @databasename

end

fetch next from cur01 into @databasename;

end;

close cur01

deallocate cur01

go

print '*********************************'

print '                             '

print '                             '

print '                             '

print '----------------------------'

print '4.1所有数据库的index          '

print '----------------------------'

print '*********************************'

use master

go

BEGIN

CREATE TABLE #INDEXFRAGINFO

(

DatabaseName nvarchar(128),

DatabaseID smallint,

full_obj_name nvarchar(384),

index_id INT, 

[name] nvarchar(128), 

index_type_desc nvarchar(60), 

index_depth tinyint,

index_level tinyint,

[AVG Fragmentation] float, 

fragment_count bigint,

[Rank] bigint 

)

DECLARE @command VARCHAR(1000)