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))
 
SET NOCOUNT ON
 
DECLARE @SearchTableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @SearchTableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
WHILE @SearchTableName IS NOT NULL
 
BEGIN
    SET @ColumnName = ''
    SET @SearchTableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @SearchTableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )
 
    WHILE (@SearchTableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
         
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            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
         
        BEGIN
            INSERT INTO #Output
            EXEC
            (
                'SELECT ''' + @SearchTableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) FROM ' + @SearchTableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END   
END
 
SELECT TableLocation, Details_Information FROM #Output
 
DROP TABLE #Output

how to get restore history in sql server

SELECT [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[backup_finish_date],
[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];
GO
— Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE [SampleDB]
SET RECOVERY SIMPLE;
GO
— Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (‘SampleDB_log’, 1);
GO
— Reset the database recovery model.
ALTER DATABASE [SampleDB]
SET RECOVERY FULL;
GO

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 setup selenium WebDriver with Nunit and Visual Studio (C#)

Selenium is an open source software tools which supporting tests automation. Now days, Software automation tools are becoming popular. Software automation tools are a time efficient. You will find different kinds of software automation tools, such as Load Runner, QTP, MS Test Manager, Jmeter etc. Selenium webdriver is a one of them. It is most popular automation tools.

 

Today, I am going to discussed about how to run selenium WebDriver with Nunit and Visual Studio (C#)

 

  • Download Nunit (http://www.nunit.org) and install in your system
  • Download selenium web-driver (http://docs.seleniumhq.org/download)
  • Open a new project and Create a Class Library
  • Add reference webdriver dll (selenium-dotnet-2.35.0 > net40) and nunit dll (Where you installed).

Webdriver dll

Nunit Dll

Or you can add Nunit dll from assemblies > Extension

  • When all of this is done and you can start write a code

Point 1:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using NUnit.Framework;

using OpenQA.Selenium;

using OpenQA.Selenium.Firefox;

using OpenQA.Selenium.Internal;

using OpenQA.Selenium.Support.UI;

 

Point 2:

namespace Automation

{
[TestFixture]
public class framework
{
private IWebDriver driver;
[SetUp]

public void LoadDriver()
{
Console.WriteLine("Check SetUp");
driver = new FirefoxDriver();
// To Maximise browser
 driver.Manage().Window.Maximize();
}

[Test]
public void Login()
{
driver.Navigate().GoToUrl("http://localhost/nopcommerce/login");
Console.WriteLine("Check URL");

// Type UserName
driver.FindElement(By.Id("Email")).SendKeys("admin@yourStore.com");

// Type Password
driver.FindElement(By.Id("Password")).SendKeys("admin");

// Clicked on Login Button
driver.FindElement(By.CssSelector("input.button-1.login-button")).Click();
}

[TearDown]
public void UnloadDriver()
{
Console.WriteLine("TearDown");
driver.Quit();
}
}
}

Above marked area. Just described how you will get id from login page

  • Build your application or press (Ctrl + Shift + B)
  • You have to run Nunit window to run this code.
  • Now, open a project from file menu and choose the project dll file or you can drag and drop the dll.
  • Click on ‘Run’ button to define result pass / fail