SQL : Search text from all Tables of a database in SQL Server

SQL : Search text from all Tables of a database in SQL Server

Here is the store procedure which will search text from all the tables of a current database in microsoft sql server.

Store Procedure

CREATE PROC SearchAllTables ( @SearchStr nvarchar(100) ) AS 
BEGIN 
    
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630)) 

SET NOCOUNT ON 

DECLARE @TableName nvarchar(256), 
        @ColumnName nvarchar(128), 
        @SearchStr2 nvarchar(110) 
        
SET @TableName = '' 
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''') 

WHILE @TableName IS NOT NULL 
BEGIN 
     SET @ColumnName = '' 
     SET @TableName = ( SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) 
                        FROM INFORMATION_SCHEMA.TABLES 
                        WHERE TABLE_TYPE = 'BASE TABLE' 
                              AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName 
                              AND OBJECTPROPERTY( OBJECT_ID( QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) ), 'IsMSShipped' ) = 0 
                      ) 
                              
 WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) 
 BEGIN 
      SET @ColumnName = ( SELECT MIN(QUOTENAME(COLUMN_NAME)) 
                          FROM INFORMATION_SCHEMA.COLUMNS 
                          WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2) 
                                AND TABLE_NAME = PARSENAME(@TableName, 1) 
                                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') 
                                AND QUOTENAME(COLUMN_NAME) > @ColumnName ) 
       IF @ColumnName IS NOT NULL 
       BEGIN 
             INSERT INTO #Results 
             EXEC ( 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 ) 
       END 
 END 
 END 
 
 SELECT ColumnName, ColumnValue FROM #Results 
 
 END

How to use SP

exec SearchAllTables 'myserchtext'


Hope this will help you and save your time.

Enjoy !!!

:)

SQL : Hierarchy chain (reverse path)

SQL : Hierarchy chain (reverse path)

Hierarchy chain (reverse path)

Create Table

 CREATE TABLE [dbo].[employee](
 [EmpName] [varchar](50) NULL,
 [EmpID] [int] NULL,
 [ManagerID] [int] NULL
) ON [PRIMARY]
GO

Insert Data

INSERT [dbo].[employee] ([EmpName], [EmpID], [ManagerID]) VALUES (N'Saurav', 1, 0)
INSERT [dbo].[employee] ([EmpName], [EmpID], [ManagerID]) VALUES (N'Dravid', 2, 1)
INSERT [dbo].[employee] ([EmpName], [EmpID], [ManagerID]) VALUES (N'Kapila', 3, 2)
INSERT [dbo].[employee] ([EmpName], [EmpID], [ManagerID]) VALUES (N'Pranil', 4, 2)
INSERT [dbo].[employee] ([EmpName], [EmpID], [ManagerID]) VALUES (N'Rohini', 5, 4)
INSERT [dbo].[employee] ([EmpName], [EmpID], [ManagerID]) VALUES (N'Peeter', 6, 0)
INSERT [dbo].[employee] ([EmpName], [EmpID], [ManagerID]) VALUES (N'Keveen', 7, 6)
GO

Create a hierarchy chain (reverse path) for specific node in a tree

;with CTE(EmpID, empName, ManagerID)
AS
(
 SELECT EmpID, empName, ManagerID 
 FROM Employee F
 WHERE EmpID = 5
 UNION ALL
 SELECT F.EmpID, F.empName, F.ManagerID  
 FROM Employee F
 INNER JOIN CTE FH ON FH.ManagerID = F.EmpID  
)
SELECT * FROM CTE

Create a hierarchy chain for specific node in a tree with all child levels

WITH empCTE
AS ( SELECT EmpID, empName, ManagerID
FROM Employee
WHERE empid = 1 -- and ManagerID = 0 
UNION ALL
SELECT e.EmpID, e.empName, e.ManagerID
FROM Employee e
INNER JOIN empCTE ON e.ManagerID = empCTE.EmpID
WHERE e.ManagerID != 0)
SELECT *
FROM empCTE

Hope this will help you and save your time.

Enjoy !!!

:)

The transaction log for database 'dbname' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

The transaction log for database 'dbname' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Issue:
InnerException: System.Data.SqlClient.SqlException (0x80131904): The transaction log for database 'dbname' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases


Analysys:

The issue says that the transaction log has reached its configured size or disk does not have sufficient space. , so we need to make space.

Solution 1:

Clear the log file and freeing disk space

Use [dbname]

Find log file name using below query and pass it in DBCC SHRINKFILE query 
Select * from sys.database_files

ALTER DATABASE [dbname] SET RECOVERY SIMPLE

DBCC SHRINKFILE ('dbname_Log', 1)

Solution 2:

Move log file on another drive

Please follow steps mentioned in below article,
http://anrorathod.blogspot.com/2018/02/how-to-move-or-change-datamdf-file-or.html

For more information you can refer, 

Troubleshoot a Full Transaction Log (SQL Server Error 9002)


https://docs.microsoft.com/en-us/sql/relational-databases/logs/troubleshoot-a-full-transaction-log-sql-server-error-9002?view=sql-server-2017

Hope this will help you and save your time.

Enjoy !!!

:)

SQL Server - Cursor example

SQL Server - Cursor example

Sometimes we need to run loop on data, for that we need cursor. We can write our own business logic in cursor loop. 

Cursor is nothing but it is a loop like development code.

Here, I have mentioned cursor syntax with example. I hope you can use cursor easily as per your requirement...

-- Declare field variable
DECLARE @id INT
DECLARE @fname nvarchar(50)
DECLARE @lname nvarchar(50)
DECLARE @email nvarchar(50)

-- Declare Cursor name
DECLARE @curEmployee CURSOR

-- Set Cursor value
SET @curEmployee = CURSOR FOR
 SELECT id, EmailId, Firstname, LastName FROM Employee

OPEN @curEmployee

-- Loop Cursor
FETCH NEXT
 FROM @curEmployee INTO @id, @email, @fname, @lname
 WHILE @@FETCH_STATUS = 0
 BEGIN
  PRINT convert(nvarchar(10) , @id) + ' - ' + @fname + ' ' + @lname + ' -- ' + @email
  -- TODO
  -- Do your process 
 FETCH NEXT
 FROM @curEmployee INTO @id, @email, @fname, @lname
END

-- Close and deallocate Cursor
CLOSE @curEmployee
DEALLOCATE @curEmployee
 

 

Hope this will help you and save your time.

Enjoy !!!

:)

SQL Backup using Powershell Script

SQL Backup using Powershell Script

 Take SQL Server database backup using powershell script....

1. Create SQL Server database backup 
2. Compress backup file in zip file
3. Upload backup zip file on Azure blob storage 

cls
#Set Variables
$Server = "localhost"
$Database = "database-name"
$BackupBaseFolder = "D:\backup\"
$BackupFolder = "D:\backup\"
$dt = get-date  -format MM-dd-yyyy-hh-mm-ss
$directoryName = "$($Database)_db_$($dt)"
$BackupFolder = "$($BackupFolder)$($directoryName)\"
$FilePath = "$($BackupFolder)$($Database)_db_$($dt).bak"

#Create backup folder 
md $BackupFolder
 
#Call SQL Command
Backup-SqlDatabase -ServerInstance $Server -Database $Database -BackupFile $FilePath 

#Create zip file
$source = $BackupFolder
$directoryPath = Join-Path $BackupFolder $directoryName
$destination = Join-Path $BackupBaseFolder "$directoryName.zip" 

If(Test-path $destination) {Remove-item $destination}
Add-Type -assembly "system.io.compression.filesystem"
[io.compression.zipfile]::CreateFromDirectory($Source, $destination) 

#Get files list from folder
$folders = Get-ChildItem $BackupBaseFolder -Directory
foreach($folderName in $folders)
{  
 $folder = $BackupBaseFolder + "\" + $folderName
 Remove-Item -recurse -force $folder #-erroraction silentlycontinue  
}

#Upload Backup Script on Azure blob storage

$localFileDirectory = "D:\backup\"
$StorageAccountName = "storageAccountname" 
$StorageAccountKey = "AccountKey"
$ContainerName = "ContainerName"

$ctx = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $StorageAccountKey

#Create container 
#New-AzureStorageContainer -Name $ContainerName -Context $ctx -Permission Blob
 
#Get files list from folder
$files = Get-ChildItem $localFileDirectory *.zip

#process each file
foreach($fileName in $files)
{  
 $localFile = $localFileDirectory + $fileName
 
 $azblob = Get-AzureStorageBlob -Blob $fileName -Container $ContainerName -Context $ctx -ErrorAction SilentlyContinue
 
 If ($azblob -ne $null) {
            # Blob already exists, check the lastwrite metadata
            Write-Host $fileName " file exists"
        } Else {
            
            #Upload file in storage
   Set-AzureStorageBlobContent -File $localFile -Blob $fileName -Container $ContainerName  -Context $ctx  -Force -ErrorAction SilentlyContinue
   #Remove file after upload file
   Remove-Item $localFile
        }
}

Write-Host "Backup file uploaded on storage..."
 

Hope this will help you and save your time.

Enjoy !!!

:)

SQL Server - Find Foreign key on table column

SQL Server - Find Foreign key on table column

How to find foreign key on specific column in the table in SQL Server. Below is the script...

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
where tab1.name='tablename'
and  col1.name ='columnname'

 Hope this will help you.

Enjoy !!!

:)

How to move or change data(.mdf) file or log(.ldf) file in SQL Server

How to move or change data(.mdf) file or log(.ldf) file in SQL Server

Sometime we need to change database data (.mdf) and log(.ldf) file for maintenance purpose or server transfer purpose. There are two ways to do this.

Way 1: Using SQL Script

-- First get physical file path of data (.mdf) and log (.ldf)
-- set active database which you want to get file path, here in my case I am using Demo database

USE Demo
GO

-- This will give physical file path of current database
sp_helpfile
GO

-- Now, Set database to single user mode and detach database with disconnecting active sessions

Use MASTER
GO

-- Set database to single user mode
ALTER DATABASE demo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

-- Detach the database
sp_detach_db 'Demo'
GO

-- This will detach databse
EXEC SP_DETACH_DB @dbname = 'Demo', @skipchecks = 'true';


-- Now move .mdf or .ldf file on your preferred location 
-- Attach database with log file at new location (Here I have moved log file only, if you want to mo

USE master
GO

-- Now Attach the database
sp_attach_DB 'Demo', 
'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\Demo.mdf',
'C:\FolderName\SQL\Demo_log.ldf'

Way 2: Using SQL Management Studio user interface

1. Right click on database which you want to move data or log file

2. Select Detach option

3. Select checkbox for "Drop Connections" in opened window and then click OK button

4. Now move .mdf or .ldf file on your preferred location 

5. Now you will not see your database in Object explorer

--- Attach database

6. Right click on database and select attach option

7. It will open a popup window

8. Click on "Add..." button in "Databases to attach" 

9. It will open another popup to select database name which you want to attach, here now you have to select data (.mdf) file and click OK button 

10. Now you will see database file location in first window popup, if it is not correct file location then select correct location 

11. Finally click on "OK" button, and your database will be attached in couple of seconds.

Hope, this will help to access your database from new location.

Enjoy !!!

:)

How to exit from single-user mode in SQL Server

How to exit from single-user mode in SQL Server

SQL Server opens multiple connections to serve data to client for their application.

So, you need to kill all opened connections before changing the access mode.

First, make sure the object explorer is pointed to a system database like master.

Second, execute a sp_who2 and find all the connections to database 'my_db'. Kill all the connections by doing KILL { session id } where session id is the SPID listed by sp_who2.

Third, open a new query window.

Then, below script will help to find all current process of sql server and kill and then it will set the database in MULTI_USER mode.

USE master
GO

DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses 
WHERE spid > 50 AND dbid = DB_ID('<Your_DB_Name>')
EXEC(@kill);
GO

SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Hope, this will help to access your database.

Enjoy !!!

:)