Rebuild all indexes for all tables and all databases

Rédigé par M Sozezzo - - Aucun commentaire

Maintain database indexes

You can just create script or run right now the reindex.
Verify the configuration to filter databases or to change the FILLFACTOR.


PRINT '-- ***************************************** --'
PRINT '-- Reindex all tables'
PRINT '-- on selected databases'
PRINT '-- ***************************************** --'
PRINT '--'
PRINT '/*'
PRINT @@servername;
PRINT Getdate();
PRINT '*/'
SET NOCOUNT ON;

-- CONFIGURATION
DECLARE @runScriptRightNow INT = 0;
DECLARE @fillfactor AS NVARCHAR(10) = '99' -- occuped% -- https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index
BEGIN TRY DROP TABLE #dbs; END TRY BEGIN CATCH END CATCH;
SELECT [name] INTO #dbs FROM sys.databases
WHERE database_id > 4
AND name NOT IN ('MyDatabaseNoReindex')
ORDER BY [name]

-- CONFIGURATION

USE [MASTER];

DECLARE @dbname nvarchar(200);
DECLARE @sql NVARCHAR(MAX);
DECLARE @ToKill AS NVARCHAR(MAX);

DECLARE @templateSelectTable AS NVARCHAR(MAX) = 'SELECT ''['' + table_catalog + ''].['' + table_schema + ''].['' + table_name + '']'' as tableName FROM [$(dbname)].INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE''' 
DECLARE @templateReindex AS NVARCHAR(MAX) ='ALTER INDEX ALL ON $(tablename) REBUILD WITH (FILLFACTOR = $(fillfactor));'
SET @templateReindex = REPLACE(@templateReindex,'$(fillfactor)',@fillfactor)
 
DECLARE @table AS table (TableName nvarchar(256))
DECLARE @script AS table (Idx int identity,SqlScript nvarchar(max))
DECLARE @Idx int;
DECLARE @SqlScript nvarchar(max)
WHILE (EXISTS(SELECT * FROM #dbs))
BEGIN

    SELECT TOP 1 @dbname = name FROM #dbs;
    DELETE FROM #dbs     WHERE name = @dbname;

 SET @sql = REPLACE(@templateSelectTable,'$(dbname)',@dbname);
 DELETE FROM @table;
 INSERT INTO @table (TableName)
 EXEC(@sql)

 INSERT INTO  @script(SqlScript)
 select REPLACE(@templateReindex,'$(tablename)',TableName) from  @table;

 WHILE (EXISTS(SELECT * FROM @script))
 BEGIN
  SELECT TOP 1 @Idx = Idx, @SqlScript = SqlScript FROM @script t;
  DELETE FROM @script WHERE Idx = @Idx;
  
  IF (@runScriptRightNow = 1)
  BEGIN
   RAISERROR (@SqlScript, 10, 1) WITH NOWAIT;
   EXEC (@SqlScript);
  END
  ELSE
  PRINT @SqlScript
 END

END

 

 

Sources :

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/specify-fill-factor-for-an-index
https://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/

 

Create backup operator

Rédigé par M Sozezzo - - Aucun commentaire

Create backup operator login

PRINT '-- Create backup operator login'

DECLARE @loginName as NVARCHAR(256) = N'UserDbBackupOperator'
DECLARE @password as nvarchar(128) = N'MyDbBackupStrongPassword'

DECLARE @sql AS NVARCHAR(MAX)
If not Exists (select loginname from master.dbo.syslogins where name = @loginName )
Begin
    Select @SQL = 'CREATE LOGIN ' + QUOTENAME(@loginName) + 'WITH PASSWORD=N'''+@password+''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
    EXEC sp_executesql @sql
End
select @sql = 'USE [?];IF (NOT EXISTS(SELECT * FROM [?].sys.database_principals WHERE name = '''+@loginName+''')) BEGIN CREATE USER ['+@loginName+'] FOR LOGIN ['+@loginName+'];ALTER ROLE [db_backupoperator] ADD MEMBER ['+@loginName+']; END';
EXEC sp_MSforeachdb @sql
go

 

Just add backup operator login over all databases


PRINT '-- Add backup operator login over all databases'

DECLARE @loginName as NVARCHAR(256) = N'UserDbBackupOperator'
DECLARE @sql AS NVARCHAR(MAX)
select @sql = 'USE [?];IF (NOT EXISTS(SELECT * FROM [?].sys.database_principals WHERE name = '''+@loginName+''')) BEGIN CREATE USER ['+@loginName+'] FOR LOGIN ['+@loginName+'];ALTER ROLE [db_backupoperator] ADD MEMBER ['+@loginName+']; END';
EXEC sp_MSforeachdb @sql
go

 

 

Classé dans : sqlscript - Mots clés : aucun
Fil RSS des articles