SQL Server

Ola Hallengren SQL-script to simplify backups, index and optimization:
https://ola.hallengren.com/

Stored procedure "mtbSearchDatabase" for searching all tables/columns after specific string:
https://www.mytecbits.com/microsoft/sql-server/search-the-whole-database-for-a-value

Below is a modified copy of "mtbSearchDatabase". 

CREATE PROCEDURE mtbSearchDatabase
(
    @SearchStr nvarchar(100), @distinctTables bit = 0
)
AS
BEGIN
	-- 231016: Added join of INFORMATION_SCHEMA.TABLES with criteria, so that view data will not be searched.
	-- (This is to avoid any corrupt views that stops the procedure from finishing..)
	-- 240507: Added sorting of response, and parameter to enable only return distinct table-names.
    SET NOCOUNT ON
    DECLARE @SearchResults TABLE(TableAndColumnName nvarchar(512), ColumnValue nvarchar(max));
    DECLARE @TableName nvarchar(256), @ColumnName nvarchar(256), @TableAndColumnName nvarchar(512),
        @TableAndColumnName2 nvarchar(512), @SearchStr2 nvarchar(110);
 
    SET @TableAndColumnName = '';
    SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''');
 
    WHILE @TableAndColumnName IS NOT NULL
    BEGIN
        SELECT TOP 1 @TableAndColumnName = QUOTENAME(isc.TABLE_SCHEMA) + '.' + QUOTENAME(isc.TABLE_NAME)+ '.' + QUOTENAME(COLUMN_NAME),
                @TableName = QUOTENAME(isc.TABLE_SCHEMA) + '.' + QUOTENAME(isc.TABLE_NAME), 
                @ColumnName = QUOTENAME(COLUMN_NAME)
            FROM INFORMATION_SCHEMA.COLUMNS isc WITH (NOLOCK) left outer join INFORMATION_SCHEMA.TABLES ist on 
			 isc.TABLE_CATALOG=ist.TABLE_CATALOG and isc.TABLE_SCHEMA=ist.TABLE_SCHEMA and isc.TABLE_NAME=ist.TABLE_NAME
            WHERE OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(isc.TABLE_SCHEMA) + '.' + QUOTENAME(isc.TABLE_NAME)
                        ), 'IsMSShipped'
                ) = 0
                AND QUOTENAME(isc.TABLE_SCHEMA) + '.' + QUOTENAME(isc.TABLE_NAME)+ '.' + QUOTENAME(COLUMN_NAME) > @TableAndColumnName
                AND DATA_TYPE IN ('char', 'nchar', 'varchar', 'nvarchar', 'text', 'ntext')
				AND ist.TABLE_TYPE='BASE TABLE'
                ORDER BY QUOTENAME(isc.TABLE_SCHEMA), QUOTENAME(isc.TABLE_NAME), QUOTENAME(COLUMN_NAME);
 
        IF @TableAndColumnName != ISNULL(@TableAndColumnName2, '')
        BEGIN
            SET @TableAndColumnName2 = @TableAndColumnName;
 
            INSERT INTO @SearchResults
            EXEC ('SELECT ''' + @TableAndColumnName + ''', ' + @ColumnName + 
                ' FROM ' + @TableName + ' WITH (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            );
        END
        ELSE
        BEGIN
            BREAK;
        END
    END
 
	IF @distinctTables = 1
		BEGIN
			SELECT distinct TableAndColumnName FROM @SearchResults ORDER BY TableAndColumnName
		END
	ELSE
		BEGIN
			SELECT TableAndColumnName, ColumnValue FROM @SearchResults ORDER BY TableAndColumnName, ColumnValue
		END
END
GO

sp_generate_inserts
Download script from: https://github.com/lambacck/generate_inserts/blob/master/generate_inserts.sql

Samples on how to use the stored procedure: 

-- To generate INSERT statements for the rest of the columns excluding images
EXEC sp_generate_inserts imgtable, @ommit_images = 1

-- To generate INSERT statements excluding (ommiting) IDENTITY columns:
EXEC sp_generate_inserts mytable, @ommit_identity = 1

sp_Blitz, sp_BlitzCache, sp_BlitzFirst, sp_BlitzIndex, and other SQL Server scripts for health checks and performance tuning.
https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/tree/main
 

Download script from: https://github.com/fededim/TSqlResources/blob/master/README.md#stored-procedures-sps-folder

Parameters:
exec spSearchTables @dbSearchPattern, @tableSearchPattern, @columnSearchPattern, @valuePattern

Examples:

exec spSearchTables NULL,NULL,NULL,NULL -- Return all tables with all columns in all databases in the server.

Script below can be used to search in just the current database:

SELECT tab.name AS [TableName], col.name AS [ColumnName]
FROM sys.columns col INNER JOIN sys.tables tab ON col.object_id = tab.object_id 
WHERE 
    col.name LIKE '%TheColumnNameHere%' 
ORDER BY [TableName], [ColumnName]

Link below to PDF-copy of free e-book "Learning SQL" (from Stack Overflow contributors).

If getting error deploy_project_internal "A required privilege is not held by the client"
when doing deploy of SSIS from Visual Studio 2022:

Resolution:
Run repair of SQL Server installation from "SQL Server 2022 Installation Center" (this should not delete any databases)
(It is necessary to have the SQL-media folder saved from installation..)

 
 

Reconnect a sql-login to a sql-user:

EXEC sp_change_users_login @Action = 'Auto_Fix', @UserNamePattern = 'user-name-here', @LoginName = null, @Password = null;

SQL-commands to do shrinking..

sp_helpdb db-name -- Shows device-names

DBCC SHRINKDATABASE (db-name, 80) -- Try to achieve 80 percent free space..
DBCC SHRINKFILE (db-name, 1);
DBCC SHRINKFILE (db-name_log, 1);

Script below can be used to find tables that have the specified column-name: 

SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS TableName, COLUMN_NAME AS ColumnName
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'column-name-here';

Sample-code to put in SSIS/DTSX "Script Task" to run custom C# code.
 

		public void Main()
		{
            // TODO: Add your code here

            // Define the file path
            string tempFolderPath = @"c:\temp";
            string filePath = Path.Combine(tempFolderPath, "ssis-log.txt");

            try
            {
                // Get the variable containing a collection of file directories
                string[] fileDirectories = (string[])Dts.Variables["User::ImportFiles"].Value; // User::FileDirectoryCollection

                // Create a string to hold the concatenated content of all files
                string concatenatedContent = "";

                // Loop through each directory
                foreach (string directory in fileDirectories)
                {
                    // Check if the directory exists
                    if (Directory.Exists(directory))
                    {
                        // Get the file names within the directory
                        string[] files = Directory.GetFiles(directory);

                        // Append the file names to the concatenated content
                        foreach (string file in files)
                        {
                            concatenatedContent += Path.GetFileName(file) + Environment.NewLine;
                        }
                    }
                    else
                    {
                        // Log a warning if the directory does not exist
                        Dts.Events.FireWarning(0, "Script Task Warning", $"Directory '{directory}' does not exist.", String.Empty, 0);
                    }
                }

                // Write the text to the file
                using (StreamWriter writer = new StreamWriter(filePath))
                {
                    writer.Write(concatenatedContent);
                }

                // Inform SSIS that the task was successful
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex)
            {
                // Log any errors
                Dts.Events.FireError(0, "Script Task Error", ex.Message, String.Empty, 0);

                // Inform SSIS that the task failed
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

Links on tracing/logging (for example using "Dts.Events.FireInformation"):
http://msdn.microsoft.com/en-us/library/ms136033.aspx
http://phil-austin.blogspot.com/2012/06/fireinformation-to-debug-ssis-script.html
 

Script to archive and delete records from "sysssislog":
 (Code originally copied from https://www.sqlservercentral.com/blogs/quick-tip-speeding-up-deletes-from-ssis-execution-log)

-- *** SCRIPT-START. Script to archive and delete data from table "sysssislog"..
DECLARE @batch_size int = 10000, -- Archive and delete in batches of 10000 records
@RowsAffected int =1,
@date_del datetime

set @date_del= DATEADD(mm,-12,getdate()); -- Time for data to keep, in our case 12 months
set @date_del = convert(date, @date_del);

IF OBJECT_ID(N'thedb..sysssislog_archive', 'U') IS NULL
 BEGIN
  select top(0) * into thedb..sysssislog_archive from thedb..sysssislog -- Create the table
   ALTER TABLE thedb..sysssislog_archive ADD CONSTRAINT PK_sysssislog_archive PRIMARY KEY CLUSTERED (id) 
	 WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
 END
 ELSE BEGIN
  print 'Table already exists.. data has been added to existing table sysssislog_archive..'
 END

SET NOCOUNT ON;
SET IDENTITY_INSERT thedb..sysssislog_archive ON

WHILE (@RowsAffected > 0)
BEGIN

insert into thedb..sysssislog_archive (id, [event], computer, operator, [source], sourceid, executionid, starttime, endtime, datacode, databytes, [message])
 select top(@batch_size) * from thedb..[sysssislog] WHERE starttime < @date_del;
delete from thedb..[sysssislog] where id in (select id from thedb..[sysssislog_archive]);
SET @RowsAffected = @@ROWCOUNT;

print @RowsAffected;

END

SET IDENTITY_INSERT thedb..sysssislog_archive OFF
SET NOCOUNT OFF;
-- *** SCRIPT-END.

Use time-interval as criteria in where-statement: 

DECLARE @fromTime VARCHAR(5) = '14:24';
DECLARE @toTime VARCHAR(5) = '15:20';

SELECT * FROM msdb..msdb_version
WHERE 
    CONVERT(TIME, GETDATE()) BETWEEN CONVERT(TIME, @fromTime) AND CONVERT(TIME, @toTime);

Script below can be used to show same info as dialog "View history" in SQL SSMS: 

SELECT 
    j.name AS JobName, h.run_date AS RunDate, h.run_time AS RunTime, h.run_duration AS RunDuration,
    CASE h.run_status
        WHEN 0 THEN 'Failed' WHEN 1 THEN 'Succeeded' WHEN 2 THEN 'Retry' 
		WHEN 3 THEN 'Canceled' WHEN 4 THEN 'In Progress' ELSE 'Unknown'
	END AS RunStatus, s.step_name AS StepName, h.step_id AS StepID, h.sql_message_id AS SqlMessageID,
	h.sql_severity AS SqlSeverity, h.[message] AS StepMessage
FROM msdb..sysjobhistory AS h 
 INNER JOIN msdb..sysjobs AS j ON h.job_id = j.job_id
 LEFT JOIN msdb..sysjobsteps AS s ON h.job_id = s.job_id AND h.step_id = s.step_id
ORDER BY RunDate DESC, RunTime DESC, JobName, StepID

Script below, modify if needed:

WITH LatestJobStatus AS ( -- Define the common table expression (CTE) to get the latest job status
    SELECT 
        job_id, run_status, run_date, run_time, [message],
        ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY run_date DESC, run_time DESC) AS RowNum
    FROM msdb.dbo.sysjobhistory
)

SELECT -- Main query to get running jobs and their status
    j.name AS JobName, ja.job_id AS JobID, ja.start_execution_date AS StartExecution, ja.stop_execution_date AS StopExecution,
    js.step_name AS StepName, js.subsystem AS Subsystem, js.command AS Command, -- For SSIS, this might contain DTSX package path
    ja.session_id AS SessionID,
    CASE 
        WHEN ls.run_status = 0 THEN 'Failed' WHEN ls.run_status = 1 THEN 'Succeeded'
        WHEN ls.run_status = 2 THEN 'Retry' WHEN ls.run_status = 3 THEN 'Canceled'
        WHEN ls.run_status = 4 THEN 'In Progress' ELSE 'Unknown'
    END AS RunStatus,
	ls.[message] AS ErrorMessage -- Add the error message column
FROM msdb.dbo.sysjobactivity AS ja INNER JOIN msdb.dbo.sysjobs AS j ON ja.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps AS js ON ja.job_id = js.job_id 
LEFT JOIN LatestJobStatus AS ls ON ja.job_id = ls.job_id AND ls.RowNum = 1
WHERE 
	j.[name] like '%' AND -- Edit this to show info on specific Job-name..
	ja.start_execution_date IS NOT NULL 
--	 AND ja.stop_execution_date IS NULL AND ja.job_history_id IS NULL -- Edit this to only show jobs that are considered to be running..
ORDER BY ja.start_execution_date DESC;

To get information on performance of individual tasks inside DTSX-packages set the "Job Step" to 
use Logging level "Performance": https://www.mattmasson.com/2011/12/what-events-are-included-in-the-ssis-catalog-log-levels/
 

When changing views containing "select *" there is a need to refresh all views that might
be using those views. Script below found on stack overflow, and can be used to create and execute "sp_refreshview".

DECLARE @Vie SYSNAME 
DECLARE cr CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT s.name + '.' + o.name 
    FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id 
    WHERE   o.type_desc='VIEW'
OPEN cr

FETCH NEXT FROM cr INTO @Vie
WHILE @@FETCH_STATUS=0
BEGIN
    PRINT 'exec sp_refreshview '+''''+@Vie+''''
    --EXEC ('exec sp_refreshview '+''''+@Vie+'''')
    FETCH NEXT FROM cr INTO @Vie
END
CLOSE cr
DEALLOCATE cr