Update values in multiple tables using single script

Below is the script used to update values in multiple tables. This is the script I used to update according to my requirement. Kindly modify it as needed.

DECLARE @oldCompanyid INT, @newcompanyid INT, @oldCompanyCode VARCHAR(50) 

        DECLARE @t Table(Name VARCHAR(100), MaxId INT)
        INSERT INTO @t(Name, MaxId)
        SELECT TableName ,maxAuditTaskId FROM dbo.AuditTaskSummary WHERE TableName = 'SourceCode' and PackageName = 'pkg_Sourcecode'
        
        SELECT @oldCompanyid = @CompanyIdOld --7
        SELECT @newcompanyid = 3
        
        CREATE TABLE #t1(RowNum INT, TableName VARCHAR(100), QueryData VARCHAR(1000))
        DECLARE @maxRow int, @minRow int
        INSERT INTO #t1 (RowNum, TableName, QueryData)
        SELECT ROW_NUMBER () OVER(ORDER BY T.name DESC) RowNum,T.name AS tableName, 
        'UPDATE ' + SCHEMA_NAME(t.schema_id) + '.' 
        + T.NAME + ' SET CompanyID  = '+CAST (@newcompanyid AS VARCHAR(10)) + ',' 
        + 'Audittaskid =' +CAST (ATS.maxAuditTaskId+1 AS VARCHAR(10)) 
        + ' WHERE CompanyID='+ CAST(@oldCompanyid AS VARCHAR(10)) +';' AS QueryData
        FROM sys.tables as T
        INNER JOIN sys.COLUMNS C ON t.OBJECT_ID = c.OBJECT_ID
        INNER JOIN (SELECT TableName ,maxAuditTaskId FROM dbo.AuditTaskSummary WHERE TableName NOT IN ('SourceCode') 
        UNION 
        SELECT Name, maxid FROM @t) ATS ON ATS.TableName = T.name
        WHERE c.name ='CompanyID' AND SCHEMA_NAME(t.schema_id) = 'dbo' 
        AND t.name NOT IN ('Company','Company_backup')
        ORDER BY SCHEMA_NAME(t.schema_id), t.name;

SELECT * FROM #t1
SELECT @maxRow = (SELECT MAX(RowNum) FROM #t1),  @minRow = (SELECT MIN(RowNum) FROM #t1)
SELECT @maxRow, @minRow

WHILE @maxRow >= @minRow
BEGIN
DECLARE @tablevalue VARCHAR(1000), @tablename VARCHAR(100)
SELECT @tablevalue = (SELECT QueryData from #t1 WHERE RowNum = @minRow), @tablename = (SELECT tablename FROM #t1 WHERE RowNum = @minRow)

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @tablename 
AND COLUMN_NAME = 'CompanyId')
EXEC (@tablevalue);
SET @minRow = @minRow +1
END
DROP TABLE #t1;

Calling the case condition inside the partition using row_number

,ROW_NUMBER() OVER (PARTITION BY CustomerId ORDER BY CASE [status]
WHEN 'active' THEN 1
WHEN 'new' THEN 2
WHEN 'cancelled' THEN 3
ELSE 4
END) AS RN

T-SQL Query to get the SQL Agent job exectuted date time and duration

Select job_name, run_datetime, run_duration
From
(
    select job_name, run_datetime,
        SUBSTRING(run_duration, 1, 2) + ':' + SUBSTRING(run_duration, 3, 2) + ':' +
        SUBSTRING(run_duration, 5, 2) AS run_duration
    from
    (
        select DISTINCT
            j.name as job_name, 
            run_datetime = CONVERT(DATETIME, RTRIM(run_date)) +  
                (run_time * 9 + run_time % 10000 * 6 + run_time % 100 * 10) / 216e4,
            run_duration = RIGHT('000000' + CONVERT(varchar(6), run_duration), 6)
        from msdb..sysjobhistory h
        inner join msdb..sysjobs j
        on h.job_id = j.job_id
    ) t
) t
order by job_name, run_datetime

To drop all tables

SQL server is providing a nice option to drop all the tables at a stretch. Below is the query to achieve the same

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

To drop single table:

EXEC sp_MSforeachtable @command1 = "DROP TABLE <TableName>"

To find the log size of all database

DBCC SQLPERF ( LOGSPACE ) 

Bulk Insert

bulk insert dbo.TestTable from 'D:\SSISFiles\Test.csv'
with (
firstrow = 2,
FIELDTERMINATOR = ',' ,
ROWTERMINATOR = '\n'
);
GO

How to convert Integer value to Time in sql server

Declare @T int

set @T = 10455836

select (@T / 1000000) % 100 as hour,

       (@T / 10000) % 100 as minute,

       (@T / 100) % 100 as second,

       (@T % 100) * 10 as millisecond

select dateadd(hour, (@T / 1000000) % 100,

       dateadd(minute, (@T / 10000) % 100,

       dateadd(second, (@T / 100) % 100,

       dateadd(millisecond, (@T % 100) * 10, cast('00:00:00' as time(2)))))) 

Structure Query Language

In Progress