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 !!!

:)

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 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 !!!

:)

SQL Server - Auto Backup of Database

SQL Server  - Auto Backup of Database

Below is the script to generate sql database backup files for selected database.

 

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[AutoDBBackup]
AS
BEGIN
DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'd:\Backup\'  
 
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 
 
DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases 
WHERE name IN ('database1', 'database2')  -- backup these databases

 
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

 
WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

 
       FETCH NEXT FROM db_cursor INTO @name   
END   

 
CLOSE db_cursor   
DEALLOCATE db_cursor
END  

Now, we have to set above store procedure in sql job to take automatic backup of database on defined interval.

Enjoy !!!

:)

Linq Queries - Update data in query

Linq Queries - Update data in query

how to update an item in a list by linq

public static class UpdateExtensions
    {
        public delegate void Func<TArg0>(TArg0 element);

        public static int Update<TSource>(this IEnumerable<TSource> source, Func<TSource> update)
        {
            if (source == null) throw new ArgumentNullException("source");
            if (update == null) throw new ArgumentNullException("update");
            if (typeof(TSource).IsValueType)
                throw new NotSupportedException("value type elements are not supported by update.");

            int count = 0;
            foreach (TSource element in source)
            {
                update(element);
                count++;
            }
            return count;
        }
    }

Use update extension

var Datas = from ev in db.Events
                            join ea in db.EventAttendees on ev.EventId equals ea.EventId
                            where ea.UserId == userid
                            select new VMEvent
                            {
                                EventName = ev.EventName,
                                EventAttendeeSubmittedDate = ea.SubmittedDate.ToString(),
                                Place = ev.Place, 
                                EventAttendeeNoofMembers = ea.noofMembers.ToString() 
                             }; 

  var query = (from items in Datas
                             select items)
                          .Update(st => { st.EventDateString = st.EventDate.ToString("dd/M/yyyy", CultureInfo.InvariantCulture);  });

Enjoy !!!
:)

SQL : How to get sql server database table size

SQL : How to get sql server database table size

Introduction

Sometimes we need to get sql server database table size to manage database space.

 Using the code

use below sql script.

SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, 
    SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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
LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY t.Name, s.Name, p.Rows
ORDER BY TotalSpaceKB desc