alivarzeshi

--open transactions with text and plans
--session ID
--login name
--database context
--transaction begin time
--how many log records have been generated by the transaction
--how much log space has been taken up by those log records
--how much log space has been reserved in case the transaction rolls back
--the last T-SQL that was executed in the context of the transaction

alivarzeshi

USE [SQLdmRepository];
GO

-- Declare variables to store the names of the table and column currently being processed.
DECLARE @TableName NVARCHAR(MAX);
DECLARE @ColumnName NVARCHAR(MAX);

-- Declare a cursor to iterate over all textual columns in the database.
-- This cursor selects the name of the table and column from the system catalog views.
-- Only user tables (xtype = 'U') and specific textual column types are considered.

alivarzeshi

DECLARE @dbName NVARCHAR(255)
DECLARE @sql NVARCHAR(MAX)

-- Create a cursor to iterate over all databases except system databases
DECLARE db_cursor CURSOR FOR
SELECT name 
FROM sys.databases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb') -- Exclude system databases

OPEN db_cursor

alivarzeshi

-- Step 1: Create a temporary table to hold the index fragmentation information
IF OBJECT_ID('tempdb..#IndexFragmentation') IS NOT NULL
    DROP TABLE #IndexFragmentation;

CREATE TABLE #IndexFragmentation (
    DatabaseName NVARCHAR(128),
    SchemaName NVARCHAR(128),
    TableName NVARCHAR(128),
    IndexName NVARCHAR(128),
    Fragmentation FLOAT

alivarzeshi

-- Parameter Declarations
DECLARE @DatabaseName NVARCHAR(128);
DECLARE @SchemaName NVARCHAR(128);
DECLARE @TableName NVARCHAR(128);
DECLARE @IndexName NVARCHAR(128);
DECLARE @Fragmentation FLOAT;
DECLARE @sql NVARCHAR(MAX);

-- Create a table to log index maintenance details
IF OBJECT_ID('dbo.IndexMaintenanceLog', 'U') IS NULL

alivarzeshi

-- Declare variables to hold table information
DECLARE @SchemaName NVARCHAR(128);
DECLARE @TableName NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX);

-- Create a table to store the results
IF OBJECT_ID('tempdb..#TableRecordCounts') IS NOT NULL
    DROP TABLE #TableRecordCounts;

CREATE TABLE #TableRecordCounts (

alivarzeshi

-- Check if the ErrorLog table already exists. If it does not exist, create it.
IF OBJECT_ID('dbo.ErrorLog', 'U') IS NULL
BEGIN
    -- Create the ErrorLog table to store detailed information about errors.
    CREATE TABLE dbo.ErrorLog
    (
        ErrorLogID INT IDENTITY(1,1) PRIMARY KEY,  -- Primary key for the error log entry.
        ErrorMessage NVARCHAR(4000),               -- Detailed error message.
        ErrorNumber INT,                           -- SQL Server error number.
        ErrorSeverity INT,                         -- Error severity level.

alivarzeshi

Date/Time: 2023-03-03 08:00:00
Log Level: Information
Source: SQL Server
Message: SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.



Date/Time: 2023-03-03 18:00:00
Log Level: Information
Source: SQL Server

alivarzeshi

Date/Time: 2023-03-03 09:00:00
Log Level: Information
Source: SQL Server
Message: Database 'MyNewDatabase' created successfully.
Database Name: MyNewDatabase
Event ID: 1800


Date/Time: 2023-03-03 10:00:00
Log Level: Information

alivarzeshi

Date/Time: 2023-03-03 10:00:00
Log Level: Information
Source: Backup
Message: Database backed up. Database: MyDatabase, creation date(time): 2023/02/02(14:00:00), pages dumped: 350, first LSN: 73000000009200037, last LSN: 73000000009600001, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Backups\MyDatabase.bak'}).


Date/Time: 2023-03-03 11:00:00
Log Level: Information
Source: Restore
Message: Database restored. Database: MyDatabase, backup start date(time): 2023/03/03(10:00:00), backup end date(time): 2023/03/03(10:30:00), number of backup devices: 1, device information: (FILE=1, TYPE=DISK: {'C:\Backups\MyDatabase.bak'}).