Untitled

mail@pastecode.io avatar
unknown
plain_text
2 months ago
1.4 kB
3
Indexable
Never
До реорганизации 
DECLARE @dbname nvarchar(128)
DECLARE @sql nvarchar(max)
DECLARE db_cursor CURSOR FOR
SEL ECT name FR OM sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb')

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @dbname
    SET @sql = 'USE [' + @dbname + '];' +
                'EXEC sp_MSforeachtable ''ALT ER   INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = ON)'';' +
                'EXEC sp_MSforeachtable ''ALT ER   INDEX ALL ON ? SET (ALLOW_ROW_LOCKS = ON)'''
    EXEC (@sql)
    PRINT 'Using database: ' + @dbname
    FETCH NEXT FROM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor


И после:
DECLARE @dbname nvarchar(128)
DECLARE @sql nvarchar(max)
DECLARE db_cursor CURSOR FOR
SEL ECT name FROM sys.databases
WH ERE name NOT IN ('master','tempdb','model','msdb')

OPEN db_cursor
FETCH NEXT FR OM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @dbname
    SET @sql = 'USE [' + @dbname + '];' +
                'EXEC sp_MSforeachtable ''ALT ER   INDEX ALL ON ? SET (ALLOW_PAGE_LOCKS = OFF)'';' +
                'EXEC sp_MSforeachtable ''ALT ER   INDEX ALL ON ? SET (ALLOW_ROW_LOCKS = OFF)'''
    EXEC (@sql)
    PRINT 'Using database: ' + @dbname
    FETCH NEXT FR OM db_cursor INTO @dbname
END

CLOSE db_cursor
DEALLOCATE db_cursor
Leave a Comment