Some Important useful SQL Scripts for SQL Server

Some Important useful SQL Scripts for SQL Server.

1. Generate update statement for multiple Rows using values from existing Table.

SELECT ‘

UPDATE TABLENAME

SET COLUMNNAME = ”’ + COLUMNNAME+ ”’

WHERE KEYCOLUMNNAME= ‘ + CAST(KEYCOLUMNNAME AS VARCHAR(5))

FROM

Get result in Text – (Cntr+T)

Ex:

SELECT ‘

UPDATE Table1

SET Column1 = ”’ + Column1 + ”’

WHERE ColumnUnique = ‘ + CAST(ColumnUnique AS VARCHAR(5))

FROM Table1

2. To get the original DB name from which it is restored.

SELECT

org.database_name Org_DBName,

Restored_To_DBName,

Last_Date_Restored

FROM

msdb..backupset org

INNER JOIN

(

SELECT

backup_set_id,

Restored_To_DBName,

Last_Date_Restored

FROM

msdb..restorehistory

INNER JOIN

(

SELECT

rh.destination_database_name Restored_To_DBName,

Max(rh.restore_date) Last_Date_Restored

FROM

msdb..restorehistory rh

GROUP BY

rh.destination_database_name

) AS InnerRest

ON

destination_database_name = Restored_To_DBName AND

restore_date = Last_Date_Restored

) As RestData

ON

org.backup_set_id = RestData.backup_set_id;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s