Tuesday, August 25, 2015

Search Column In All Table SQL Server


Find column in all tables


SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%UnitPrice%'
ORDER BY schema_name, table_name;

Sunday, June 14, 2015

Restore .bak file using query

--What account are you running the SQL Server engine under? Does that account have access to the location of the backup file.


-- Can you read the file list
RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER1\MSSQL\Backup\SASDB01062015.bak'
   WITH FILE=1;
GO



-- Is this a valid backup?
RESTORE VERIFYONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SASDB01062015.bak'
   WITH FILE=1;
GO



-- Simple restore, source and target file names and location are the same
RESTORE DATABASE AdventureWorks2012
   FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\SASDB01062015.bak'
   WITH FILE = 1
   RECOVERY;

How to check the version of a SQL Server .bak file


RESTORE HEADERONLY is a useful way to get the SQL Server version of a .bak file.  It’s a way to avoid trying to restore, say, a SQL 2008 R2 backup onto a SQL 2008 server, which won't work.  I ran the command from my local SQL 2008 instance against a few different .bak files on my computer; the output fields related to version follow the commands.
RESTORE HEADERONLY FROM DISK = 'D:\dbbackup\base106.bak'
-- DatabaseVersion      SoftwareVersionMajor    SoftwareVersionMinor       SoftwareVersionBuild
-- 611                  9                           0                      4035
-- (SQL Server 2005 backup)

RESTORE HEADERONLY FROM DISK = 'D:\dbbackup\iMISMain15_15.2.5.3815.bak'
-- DatabaseVersion      SoftwareVersionMajor    SoftwareVersionMinor       SoftwareVersionBuild
-- 655                  10                          0                      2531
-- (SQL Server 2008 backup)

RESTORE HEADERONLY FROM DISK = 'D:\dbbackup\ga1520_upgrade.bak'
-- DatabaseVersion      SoftwareVersionMajor    SoftwareVersionMinor       SoftwareVersionBuild
-- 661                  10                          50                           2500
-- (SQL Server 2008 R2 backup)
-- (the header can be read, but this backup cannot be restored onto a SQL 2008 server)

RESTORE HEADERONLY FROM DISK = 'D:\dbbackup\SQl2012\SMR 999999\ABCD Bkup for ASI.bak'
-- BackupName: *** INCOMPLETE ***
-- all values NULL
-- (SQL Server 2012 backup, SQL Server 2008 or SQL Server 2008 R2 instance)
-- (the header cannot be read, and this backup cannot be restored onto a SQL 2008 server)

Tuesday, June 2, 2015

How can I run multiple Skype accounts on Windows desktop?

To use more than one Skype account on the same computer at the same time, you need to start a new instance of Skype.
  1. From the Windows taskbar, click Start > Run (or press the Windows The Windows key. and R keys on your keyboard at the same time).
  2. In the Run window, type the following command (including the quotes) and press OK:
  3. For 32-bit operating systems:
    "C:\Program Files\Skype\Phone\Skype.exe" /secondary
    For 64-bit operating systems:
    "C:\Program Files (x86)\Skype\Phone\Skype.exe" /secondary
If you get an error message, copy and paste the exact command from this page and try again.
Be aware that if you’ve changed the installation path for Skype, then you’ll need to enter the correct path for the Skype.exe file.
If the above solution fails, you can try another option:
  1. Find the Skype.exe file in C:\Program Files\Skype\Phone\ if you’re running a 32-bit operating system.
  2. If you’re running 64-bit operating system, you can find the file in C:\Program Files (x86)\Skype\Phone\.
  3. Right-click the file and select Send to > Desktop (create shortcut).
  4. Locate the shortcut on your desktop, then right-click it and select Properties.
  5. In the Target field of the Shortcut tab, add /secondary to the end of the path. The Target field should now be "C:\Program Files\Skype\Phone\Skype.exe" /secondary.
  6. Click OK. You can now start a new instance of Skype every time you double-click the new shortcut.

Saturday, January 3, 2015

Search Text In All Table SQL Server


Some times you need to find text in database and you don’t know the table and column. Using the below code you can easily find the text in your database

DECLARE
    @search_string  VARCHAR(100),
    @table_name     SYSNAME,
    @table_id       INT,
    @column_name    SYSNAME,
    @sql_string     VARCHAR(2000)

SET @search_string = 'Enter text to be search'

DECLARE tables_cur CURSOR FOR SELECT name, object_id FROM sys.objects WHERE type = 'U'

OPEN tables_cur

FETCH NEXT FROM tables_cur INTO @table_name, @table_id

WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE columns_cur CURSOR FOR SELECT name FROM sys.columns WHERE object_id = @table_id AND system_type_id IN (167, 175, 231, 239)

    OPEN columns_cur

    FETCH NEXT FROM columns_cur INTO @column_name
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        SET @sql_string = 'IF EXISTS (SELECT * FROM ' + @table_name + ' WHERE [' + @column_name + '] = ''' + @search_string + ''') PRINT ''' + @table_name + ', ' + @column_name + ''''

        EXECUTE(@sql_string)

        FETCH NEXT FROM columns_cur INTO @column_name
    END

    CLOSE columns_cur

    DEALLOCATE columns_cur

    FETCH NEXT FROM tables_cur INTO @table_name, @table_id
END

CLOSE tables_cur

DEALLOCATE tables_cur