Using JOIN in UPDATE Statement

CREATE TABLE Table1 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table1 (Col1, Col2, Col3)
SELECT 1, 11, 'First'
UNION ALL
SELECT 11, 12, 'Second'
UNION ALL
SELECT 21, 13, 'Third'
UNION ALL
SELECT 31, 14, 'Fourth'
GO
-- Create table2
CREATE TABLE Table2 (Col1 INT, Col2 INT, Col3 VARCHAR(100))
INSERT INTO Table2 (Col1, Col2, Col3)
SELECT 1, 21, 'Two-One'
UNION ALL
SELECT 11, 22, 'Two-Two'
UNION ALL
SELECT 21, 23, 'Two-Three'
UNION ALL
SELECT 31, 24, 'Two-Four'
GO
SELECT *
FROM Table1
SELECT *
FROM Table2
GO

UPDATE Table1
SET Col2 = t2.Col2,
Col3 = t2.Col3
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.Col1 = t2.Col1
--WHERE t1.Col1 IN (21, 31)

SELECT *
FROM Table1
SELECT *
FROM Table2
GO

DROP TABLE Table1
DROP TABLE Table2

Pokemon Emerald Ruby Red Gold emulation with Visual Boy Advance

1. Pokemon Emerald Ruby Red Gold emulation with Visual Boy Advance

Click the above link

SQL TRANSACTION LOG SHRINK/TRUNCATE

use [DBNAME]
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

ALTER DATABASE [DBNAME] SET RECOVERY SIMPLE
declare @fileId as int = (select file_id from sys.database_files where name like '%log%')

DBCC SHRINKFILE(@fileID, EmptyFile)
DBCC SHRINKDATABASE ([DBNAME], 0, TRUNCATEONLY);

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

IN THIS YOU CANT EASILY GET DB LOG NAME IN @FILEID



For Multiple Database, We can use Cursor like below

DECLARE @DB_Name varchar(100) 
DECLARE @Command nvarchar(200)
DECLARE @Command2 nvarchar(200)
DECLARE @Command3 nvarchar(500)
DECLARE database_cursor CURSOR FOR 
SELECT name 
FROM MASTER.sys.sysdatabases

OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name

WHILE @@FETCH_STATUS = 0 
BEGIN 
IF (@DB_Name <> 'master' AND @DB_Name <> 'tempdb' AND @DB_Name <> 'model' AND @DB_Name <> 'msdb') 
 BEGIN
--SELECT @Command = 'SELECT ' + '''' + @DB_Name + '''' + ', SF.filename, SF.size FROM ' +  @DB_Name + '.sys.sysfiles SF'
     SELECT @Command ='ALTER DATABASE [' + @DB_Name + '] SET RECOVERY SIMPLE'
     EXEC sp_executesql @Command
     Select @Command = 'use [' + @DB_Name + '] ' 
     Select @Command2 = @Command + 'SELECT name ,size/128.0 - CAST(FILEPROPERTY(name,' + ''''+  'SpaceUsed'+ '''' +') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files'
     select @Command3 = 'declare @fileId as int = (select file_id from sys.database_files where name like ''%log%'');DBCC SHRINKFILE(@fileID, EmptyFile);DBCC SHRINKDATABASE ([' +@DB_Name + '], 0, TRUNCATEONLY);'
     EXECUTE sp_executesql @Command2;EXECUTE sp_executesql @Command3;EXECUTE sp_executesql @Command2 --exec (@Command2)
  End
     FETCH NEXT FROM database_cursor INTO @DB_Name 
END

CLOSE database_cursor 
DEALLOCATE database_cursor

Batch file to delete Folder and files by setting the parameter of the folder

Sample : https://www.dropbox.com/s/cvwltunpvwqxa3l/call%201.bat%20Brighton_v45.bat?dl=0


Code:
@echo off
setlocal EnableDelayedExpansion
cls
ECHO You are running: %0 %1 %date% %time%
REM set param1=%~1
set param1=Brighton_v45
set MYDIR=C:\
set tempPath=c:\%param1%\src\ProfilingSetup
set f1="%tempPath%\Default Configuration"
set f2="%tempPath%\Script Files\*.*"
set srcfile="%tempPath%\Script Files\setup.rul*"
set destfile="%tempPath%\setup.rul*"
set srcfiles="%tempPath%\String1033.txt*"
set destfiles="%tempPath%\Script Files\String1033.txt*"
for /F %%i in ('dir /B/D %MYDIR%') do call :Foo %%i
goto END

:Foo
set z=%1
if "%z%"=="%param1%" (
REM echo z set
echo %param1%
@RD /S /Q %f1%
xcopy /f /y %srcfile% %destfile%
del /s /q %f2%
xcopy /f /y %destfile% %srcfile%
xcopy /f /y %srcfiles% %destfiles%
del /q %tempPath%
xcopy /f /y %destfiles% %srcfiles%
del /q %destfiles%
)
goto :eof


:End

Shrink Log Space in SQL

DBCC LOGINFO(DB_NAME)
BACKUP LOG [DB_NAME] TO DISK = 'E:\Program Files\Microsoft SQL Server\MSSQL\Backup\DB_NAME.bak'
DBCC SHRINKFILE(NEWDATACOLLECT_Log)--right click db goto properties then files menu there you can get log name
DBCC LOGINFO(DB_NAME)

SQL : Delete multiple tables in db having record more than 10,000

create proc myproc
as 
begin
DECLARE @name VARCHAR(50),@fileName VARCHAR(256),@deleteInt int
CREATE TABLE ##Temp
    (
        Count   integer NOT NULL,
        Name        nvarchar(50) 
    );
DECLARE db_cursor CURSOR FOR 
SELECT top 1000
 t.NAME AS TableName--,
 --i.name AS indexName,
 --SUM(p.rows) AS RowCounts,
 --SUM(a.total_pages) AS TotalPages, 
 --SUM(a.used_pages) AS UsedPages, 
 --SUM(a.data_pages) AS DataPages,
 --(SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB, 
 --(SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB, 
 --(SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM 
 sys.tables t
INNER JOIN  
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND  
 i.index_id <= 1
GROUP BY 
 t.NAME, i.object_id, i.index_id, i.name 

--ORDER BY 

---- OBJECT_NAME(i.object_id) 
--8 desc 
--SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB FROM sys.database_files order by 2; select * from sys.database_files
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name 
WHILE @@FETCH_STATUS = 0   
BEGIN  
       SET @fileName = 'select count(*),'+ '''' + @name + '''' + ' FROM dba.' + @name  
       INSERT  ##Temp (Count,Name)    
  EXEC (@fileName) 
       FETCH NEXT FROM db_cursor INTO @name   
END  
CLOSE db_cursor   
DEALLOCATE db_cursor  

DELETE FROM ##Temp where COUNT <= 10000
set @deleteInt = 0
DECLARE db_cursor1 CURSOR FOR select name from ##Temp
OPEN db_cursor1   
FETCH NEXT FROM db_cursor1 INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN 
Set @deleteInt = (select top 1 COUNT from ##Temp where Name = @name)  
SET @deleteInt = @deleteInt - 10000 
    SET @fileName = 'DELETE top(' + CAST(@deleteInt AS VARCHAR(10)) +  ')' + 'FROM dba.' + @name 
print @fileName
exec (@filename)
FETCH NEXT FROM db_cursor1 INTO @name  
END   

CLOSE db_cursor1  
DEALLOCATE db_cursor1
end
go

exec myproc