When search value in all tables under a database

If someone wants to search by specific value in database, it’s difficult to search a value whole database. Below code easy way to find out

DECLARE @SearchStr nvarchar(100)
SET @SearchStr = 'test@test.com.au'
CREATE TABLE #Output (TableLocation nvarchar(370), Details_Information nvarchar(3630))
DECLARE @SearchTableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @SearchTableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @SearchTableName IS NOT NULL
    SET @ColumnName = ''
    SET @SearchTableName = 
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @SearchTableName
            AND    OBJECTPROPERTY(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    WHILE (@SearchTableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
        SET @ColumnName =
            WHERE         TABLE_SCHEMA    = PARSENAME(@SearchTableName, 2)
                AND    TABLE_NAME    = PARSENAME(@SearchTableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        IF @ColumnName IS NOT NULL
            INSERT INTO #Output
                'SELECT ''' + @SearchTableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @SearchTableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
SELECT TableLocation, Details_Information FROM #Output

how to get restore history in sql server

SELECT [rs].[destination_database_name],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
ORDER BY [rs].[restore_date] DESC

How to clear database log write script in MS SQL Server

1. Open SQL Query
2. Type below script (Assuming Database name ‘SampleDB’)
USE [SampleDB];
— Truncate the log by changing the database recovery model to SIMPLE.
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (‘SampleDB_log’, 1);
— Reset the database recovery model.

How to check how many users are connected to a SQL Server

You can retrieve information about the number of connected users to a SQL Server. It is also possible to check which users are connected to a particular database.

Few examples in below:

Example 1: To check all users and process connection

select @@servername as server, count(distinct usr) as users, count(*) as processes from

( select sp.loginame as usr, sd.name as db

 from sysprocesses sp join sysdatabases sd on sp.dbid = sd.dbid ) as db_usage

Example 2: To check individual users and process connection

select Users, count(distinct db) as Databases, count(*) as processes from

( select sp.loginame as Users, sd.name as db

 from sysprocesses sp join sysdatabases sd on sp.dbid = sd.dbid ) as db_usage

group by Users

order by Users

Example 3: To check database, user and process connections

select db, users, count(*) as processes from

( select sp.loginame as users, sd.name as db

 from sysprocesses sp join sysdatabases sd on sp.dbid = sd.dbid ) as db_usage

where db like('%')

group by db, users

order by db, users

Database to Single-user Mode in MS SQL Server 2008

Today I will discussed about, how to restrict access to single user.

Why need restrict access database?

* Single-user mode specifies that only one user at a time can access the database and is generally used for maintenance actions.

* When multiple users are connected to the database at the time that you set the database to single-user mode, their connections to the database will be closed without warning.

How to setup database to single user mode.

Step 1: Go to Start menu > All Programs > Microsoft SQL Server 2008 > SQL Server Management Studio and Connect to database server








Step 2: Right-click the database and click Properties.

Step 3:  In Database Properties dialog box, click Options page from left menu.

Step 4: Select SINGLE_USER  from the Restrict Access option

Step 5: If other users are connected to the database, an Open Connections message will appear. To change the property and close all other connections, click Yes.

How to enable FILESTREAM in SQL Server 2008 (After Install)

If you forgot to enable FILESTREAM when install SQL Server 2008.

Don’t worry about it. You can enable FILESTREAM later.

To enable filestream settings:

1.  Go to Start menu > All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager.

2.  If asking for user account control then click yes

3. In the list of services, right-click SQL Server Services, and then click Open

4. Now go to Filestream tab and ticked ‘Enable FILESTREAM for Transact-SQL access’.

5. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.

6. If remote clients must access the FILESTREAM data that is stored on this share, select Allow remote clients to have streaming access to FILESTREAM data

7.  Click Apply

 8. Go to SQL Server Management Studio, click New Query to display the Query Editor

9. In Query Editor, enter the following Transact-SQL code:

EXEC sp_configure filestream_access_level, 2


10. Click Execute or Press ‘F5’ button

11. Make File-stream access level full

12. Restart the SQL Server service