T-SQL to Get Fixed Drives Free Space in Percentage – ms-sqlserver

 

use master

go

 

sp_configure ‘Ole Automation Procedures’, 1

reconfigure

 

go

 

SET NOCOUNT ON

DECLARE @hr int

DECLARE @fso int

DECLARE @drive char(1)

DECLARE @odrive int

DECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576

CREATE TABLE #drives (drive char(1) PRIMARY KEY,

FreeSpace float NULL,

TotalSize float NULL)

INSERT #drives(drive,FreeSpace)

EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate ‘Scripting.FileSystemObject’,@fso OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD

FOR SELECT drive from #drives ORDER by drive

OPEN dcur FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0

BEGIN

EXEC @hr = sp_OAMethod @fso,‘GetDrive’, @odrive OUT, @drive

IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr =sp_OAGetProperty @odrive,‘TotalSize’, @TotalSize OUT

IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives SET TotalSize=@TotalSize/@MB

WHERE drive=@drive FETCH NEXT FROM dcur INTO @drive

End

Close dcur

DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT drive+‘:’, CAST(CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as VARCHAR(MAX))+‘%’ as ‘Free(%)’ FROM #drives

ORDER BY drive DROP TABLE #drives Return

go

sp_configure ‘Ole Automation Procedures’, 0

reconfigure

go

Advertisements

Stored Procedure To find all users and roles on a Single/Individual Database in SQL Server 2000

Using this Stored Procedure you can get all the users in an SQL Server Instance. This Has been modified for use with SQL Server 2000 Only.
USE [master]
GO
–CHECK IF THE STORED PROCEDURE ALREADY EXISTS
IF  EXISTS
      (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[spGetUsers]’) AND type in (N’P’, N’PC’))
      DROP PROCEDURE [dbo].[spGetUsers]
GO
–NAME OF THE DATABASE ON WHICH THE STORED PROCEDURE SHOULD BE CREATED.
–GOOD TO CREATE IT ON MASTER
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
–CREATION OF STORED PROCEDURE
CREATE PROCEDURE [dbo].[spGetUsers]
AS
SET NOCOUNT ON
–VARIABLES DECLARATION
DECLARE @Count INTEGER
DECLARE @varDBName VARCHAR(50)
DECLARE @varDBid INT
DECLARE @varSQL NVARCHAR(4000)
DECLARE @login varchar(50)
DECLARE @getlogin CURSOR
–TEMPORARY TABLE TO HOLD DATABASE AND USER-DEFIINE ROLE USER NAMES
CREATE TABLE #UserDetail
(
      varServerName VARCHAR(255) DEFAULT @@SERVERNAME
      ,varDBName VARCHAR(255)
      ,varDBid INT
      ,varRoleName VARCHAR(255)
      ,varUserName VARCHAR(255)
      ,varUserID VARBINARY(255)
      ,Active CHAR(3)
)
–TEMPORARY TABLE TO DISPLAY DATA
CREATE TABLE #UserDetailDisplay
(
      varServerName VARCHAR(255) DEFAULT @@SERVERNAME
      ,varDBName VARCHAR(255)
      ,varDBid INT
      ,varRoleName VARCHAR(255)
      ,varUserName VARCHAR(255)
      ,varUserID VARBINARY(255)
      ,Active CHAR(3)
)

–TEMPORARY TABLE TO HOLD DATABASE NAMES

CREATE TABLE #DBNameDet
(
      CountID INTEGER IDENTITY(1,1)
      ,varDBName VARCHAR(50)
      ,varDBid INT
)
–TEMPORARY TABLE TO HOLD SP_WHO2 RESULTS
CREATE TABLE #spwho2
(
      spid INT
      ,status CHAR(50)
      ,login VARCHAR(255)
      ,hostname VARCHAR(50)
      ,blkby VARCHAR(50)
      ,dbname VARCHAR(255)
      ,command CHAR(200)
      ,cputime INT
      ,diskio INT
      ,lastbatch VARCHAR(255)
      ,programname VARCHAR(255)
      ,spid1 INT
      –,requestid INT
)
–OBTAIN MEMBERS OF EACH SERVER ROLE
INSERT INTO #UserDetail (varRoleName, varUserName, varUserID)
EXEC dbo.sp_helpsrvrolemember
–OBTAIN DATABASE NAMES
INSERT INTO #DBNameDet (varDBName,varDBid)
SELECT name,dbid FROM MASTER.DBO.SYSDATABASES
–IF you want to exclude any database include where else comment the same.
where name not in (”,”,”)
      SET @Count = @@ROWCOUNT
–LOOP THROUGH DATABASES TO OBTAIN MEMBERS  OF DATABASE ROLES AND USER-DEFINED ROLES
WHILE @Count > 0
BEGIN
      –GET DATABASE NAME FROM TEMPORARY TABLE
      SET @varDBName = (SELECT varDBName FROM #DBNameDet WHERE CountID = @Count)
      SET @varDBid = (SELECT varDBid FROM #DBNameDet WHERE CountID = @Count)
      –OBTAIN MEMBERS OF EACH DATABASE AND USER-DEFINED ROLE
      SET @varSQL = ‘INSERT INTO #UserDetail (varRoleName, varUserName, varUserID)
      EXEC ‘ + @varDBName + ‘.dbo.sp_helprolemember’
      EXEC sp_executesql @varSQL
      –UPDATE DATABASE NAME IN TEMPORARY TABLE
      UPDATE #UserDetail
      SET varDBName = @varDBName
      ,varDBid = @varDBid
      WHERE varDBName IS NULL
      SET @Count = @Count 1
END
–TEMPORARY TABLE TO GET VALUES FROM SP_WHO2
INSERT INTO #spwho2
(
      SPID
      ,Status,Login
      ,HostName
      ,BlkBy
      ,DBName
      ,Command
      ,CPUTime
      ,DiskIO
      ,LastBatch
      ,ProgramName
      ,SPID1
     — ,REQUESTID
)
EXEC dbo.sp_who2
–CURSUR TO FETCH OPEN USER WHO ARE CURRENTLY CONNECTED TO DATABASES
SET @getlogin = CURSOR FOR
SELECT a.login FROM
#spwho2 a
INNER JOIN #UserDetail t
      ON a.DBname = t.varDBName and a.login = t.varUserName
OPEN @getlogin
FETCH NEXT
FROM @getlogin INTO @login
WHILE @@FETCH_STATUS = 0
BEGIN
      UPDATE #UserDetail SET Active = ‘YES’ where varUserName in(SELECT a.login FROM
      #spwho2 a
      INNER JOIN #UserDetail t
      ON a.DBname = t.varDBName and a.login = t.varUserName)
      FETCH NEXT
      FROM @getlogin INTO @login
END
CLOSE @getlogin
DEALLOCATE @getlogin
UPDATE #UserDetail set active = ‘NO’ where active IS NULL
–INSERT DATA INTO TEMPORARY TABLE TO DISPLAY
INSERT INTO #UserDetailDisplay
SELECT tgus.* FROM #UserDetail tgus
LEFT JOIN #UserDetailDisplay tdus
ON tgus.varUserName = tdus.varUserName
AND tgus.varDBName = tdus.varDBName
AND tgus.varRoleName = tdus.varRoleName
AND tgus.varServerName = tdus.varServerName
WHERE tdus.varServerName IS NULL
–SELECT THE RECORDS BY USER–YOU CAN CHANGE AS PER YOUR CONVINIENCE
PRINT ‘Display by User’
SELECT varUserName as UserName, varDBName as DBName, varRoleName as RoleName, varServerName as ServerName,Active FROM #UserDetailDisplay
WHERE varUserName<>‘dbo’
ORDER BY varUserName
–DROPPING THE TEMPORARY TABLE CREATED
DROP TABLE #UserDetailDisplay
DROP TABLE #UserDetail
DROP TABLE #spwho2
DROP TABLE #DBNameDet
GO

Authenticate Issue in SQL Server due to SID

Found this to be useful so sharing the same.

User is unable to access the database even though his account is existing in the Group and having relevant access.

Cause:

Before Moving to one of the cause let me take you through how authenticate process is done for authenticating to SQL Server using a Domain Account

Process of SQL Authentication:

Whenever a Domain User is logged on to a Machine his SID is being cached in the Machine. When this User Authenticates with SQL Server the below mentioned process is carried out for Authentication.

  1. Domain User ID and Password along with SID for Domain Account are passed in.
  2. The User ID and Passwords along with SID received are Authenticated with the User ID and Password along with SID which is existing on SQL Server.
  3. When all there are verified successfully the respective existing access is granted to that particular user.

Why the problem is occurred:

Basically the groups in Windows can be classified as Domain Global and Domain Local.

The existing Domain Groups may on Domain either of the Hierarchy i.e. Domain Global and Domain Local. These Domain Group if dropped and Same Domain groups were created at Domain Local / Global Hierarchy then u will come across the issue for sure if this activity is done only at windows level but not at SQL Server.

Now here is where the tricky Part Lies.

Whenever a Domain Account or Group is dropped and Re-Created a New SID is assigned to that Login.

So when this New Login with same name is trying to authenticate with SQL Server the Authentication fails because only the User ID and Password match with that of existing in SQL Server but not the SID because At windows level it is having the new SID and at SQL Server the Old SID which one is Prior to the New Windows login is created.

To resolve the Issue the existing Group/Logins on SQL Server Needs to be dropped and Re-Created so that the New SID gets Synced and Updated and the Authentication can be done successfully.

You can get the Invalid Logins using Stored Procedure sp_validatelogins. But again this is not trust worthy because if an it checks only for SID Matching. Suppose if a Login is dropped and created again this will not show correct results as it compares only SID.

Stored Procedure To find all users and roles on a Single/Individual Database

— ======================================================================================

— Author Name: Manjunath C Bhat

— Author EMail ID: manjunathcbhat@gmail.com

— Author Designation: Senior Database Engineer

— Stored Procedure Purpose: To find all users and roles on a Single/Individual Database

— Stored Procedure Name: spGetDBUsers

— Parameter1: @DatabaseName

— ======================================================================================

— Stored Procedure To find all users and roles on a Single/Individual Database

— ======================================================================================

USE

[YOURDATABASENAME] GO

–CHECK IF THE STORED PROCEDURE ALREADY EXISTS

IF

EXISTS  

(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[spGetDBUsers]’) AND type in (N’P’, N’PC’)) 

DROP PROCEDURE [dbo].[spGetDBUsers]GO

–NAME OF THE DATABASE ON WHICH THE STORED PROCEDURE SHOULD BE CREATED.

–GOOD TO CREATE IT ON MASTER

USE

[YOURDATABASENAME] GO

SET

GO

SET

GO

ANSI_NULLS ON QUOTED_IDENTIFIER ON–CREATION OF STORED PROCEDURE

CREATE

@DatabaseName

PROCEDURE [dbo].[spGetDBUsers]VARCHAR(255)AS

SET

NOCOUNT ON–VARIABLES DECLARATION

DECLARE

DECLARE

@Count INTEGER @varDBName VARCHAR(50)DECLARE

DECLARE

@varDBid INT @varSQL NVARCHAR(4000)DECLARE

@login varchar(50)DECLARE

@getlogin CURSOR–TEMPORARY TABLE TO HOLD DATABASE AND USER-DEFIINE ROLE USER NAMES

CREATE

TABLE #UserDetail(

varServerName

VARCHAR(255) DEFAULT @@SERVERNAME 

,varDBName VARCHAR(255) ,varDBid INT 

,varRoleName VARCHAR(255) 

,varUserName VARCHAR(255) 

,varUserID VARBINARY(MAX) 

)

,Active CHAR(3)–TEMPORARY TABLE TO DISPLAY DATA

CREATE

TABLE #UserDetailDisplay(

varServerName

VARCHAR(255) DEFAULT @@SERVERNAME 

,varDBName VARCHAR(255) ,varDBid INT 

,varRoleName VARCHAR(255) 

,varUserName VARCHAR(255) 

,varUserID VARBINARY(MAX) 

)

,Active CHAR(3)–TEMPORARY TABLE TO HOLD DATABASE NAMES

CREATE

TABLE #DBNameDet(

CountID

INTEGER IDENTITY(1,1) 

,varDBName VARCHAR(50) 

,varDBid INT)

–TEMPORARY TABLE TO HOLD SP_WHO2 RESULTS

CREATE

TABLE #spwho2(

spid

INT 

,status CHAR(50) 

,login VARCHAR(255) 

,hostname VARCHAR(50) 

,blkby VARCHAR(50) 

,dbname VARCHAR(255) 

,command CHAR(200) 

,cputime INT 

,diskio INT 

,lastbatch VARCHAR(255) 

,programname VARCHAR(255) 

,spid1 INT 

,requestid INT)

–OBTAIN MEMBERS OF EACH SERVER ROLE

INSERT

INTO #UserDetail (varRoleName, varUserName, varUserID)EXEC

dbo.sp_helpsrvrolemember–OBTAIN DATABASE NAMES

INSERT

INTO #DBNameDet (varDBName,varDBid)SELECT

name,dbid FROM MASTER.DBO.SYSDATABASES WHERE name = @DatabaseName –LOOP THROUGH DATABASES TO OBTAIN MEMBERS OF DATABASE ROLES AND USER-DEFINED ROLES

BEGIN

 

–GET DATABASE NAME FROM TEMPORARY TABLE 

SET @varDBName = (SELECT varDBName FROM #DBNameDet) 

–OBTAIN MEMBERS OF EACH DATABASE AND USER-DEFINED ROLE 

EXEC ‘

SET @varSQL = ‘INSERT INTO #UserDetail (varRoleName, varUserName, varUserID) + @varDBName + ‘.dbo.sp_helprolemember’ 

EXEC sp_executesql @varSQL–UPDATE DATABASE NAME IN TEMPORARY TABLE 

UPDATE #UserDetailSET varDBName = @varDBName,varDBid = @varDBidWHERE varDBName IS NULLEND

–TEMPORARY TABLE TO GET VALUES FROM SP_WHO2

INSERT

INTO #spwho2(

SPID

,Status,Login 

,HostName,BlkBy,DBName,Command,CPUTime,DiskIO,LastBatch,ProgramName,SPID1,REQUESTID)

EXEC

dbo.sp_who2–CURSUR TO FETCH OPEN USER WHO ARE CURRENTLY CONNECTED TO DATABASES

SET

SELECT

#spwho2 a

@getlogin = CURSOR FOR a.login FROM INNER

JOIN #UserDetail tON a.DBname = t.varDBName and a.login = t.varUserNameOPEN

@getloginFETCH

FROM

NEXT @getlogin INTO @loginWHILE

@@FETCH_STATUS = 0BEGIN

 

#spwho2 a

UPDATE #UserDetail SET Active = ‘YES’ where varUserName in(SELECT a.login FROM INNER JOIN #UserDetail tON a.DBname = t.varDBName and a.login = t.varUserName) 

FETCH NEXT 

FROM @getlogin INTO @loginEND

CLOSE

@getloginDEALLOCATE

@getloginUPDATE

#UserDetail set active = ‘NO’ where active IS NULL–INSERT DATA INTO TEMPORARY TABLE TO DISPLAY

INSERT

INTO #UserDetailDisplaySELECT

tgus.* FROM #UserDetail tgusLEFT

JOIN #UserDetailDisplay tdusON

tgus.varUserName = tdus.varUserNameAND

tgus.varDBName = tdus.varDBNameAND

tgus.varRoleName = tdus.varRoleNameAND

tgus.varServerName = tdus.varServerNameWHERE

tdus.varServerName IS NULL–SELECT THE RECORDS BY USER–YOU CAN CHANGE AS PER YOUR CONVINIENCE

PRINT

‘Display by User’SELECT

varUserName as UserName, varDBName as DBName, varRoleName as RoleName, varServerName as ServerName,Active FROM #UserDetailDisplayWHERE

varUserName <> ‘dbo’ORDER

BY varUserName–DROPPING THE TEMPORARY TABLE CREATED

DROP

TABLE #UserDetailDisplayDROP

TABLE #UserDetailDROP

TABLE #spwho2DROP

TABLE #DBNameDetGO

Stored Procedure to Monitor Database and Powershell Script to Monitor Database and Blocking

— ==========================================================

— Author Name: Manjunath C Bhat

— Author EMail ID: manjunathcbhat@gmail.com

— Author Designation: Senior Database Engineer

— Stored Procedure Purpose: To Monitor the Database Activity

— Stored Procedure Name: SQLActivityMonitor

— Parameter1: @MaxBlockWaitTime Type Int

— Parameter2: @StatusCode Type TinyInt OUTPUT

— Parameter3: @StatusMessage Type Varchar OUTPUT

— ==========================================================

— Stored Procedure To Monitor SQLDatabaseActivity

— ==========================================================

— Drop stored procedure if it already exists

IF EXISTS (

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE SPECIFIC_SCHEMA = N’Dbo’

AND SPECIFIC_NAME = N’SQLActivityMonitor’

)

DROP PROCEDURE [DBO].[SQLActivityMonitor]

GO

CREATE PROCEDURE [DBO].[SQLActivityMonitor]

@MaxBlockWaitTime INT,

@StatusCode TINYINT OUTPUT,

@StatusMessage VARCHAR(8000) OUTPUT

AS

BEGIN

— SET NOCOUNT ON added to prevent extra result sets from

— interfering with SELECT statements.

SET NOCOUNT ON;

–Determine # of Active Connections, Open Transactions and Blocked Transaction

DECLARE @BlockedCount INT

DECLARE @OpenTranCount INT

DECLARE @ConnectionCount INT

DECLARE @ExceptionBlockedCount INT

SELECT @ConnectionCount=COUNT(*) FROM MASTER..SYSPROCESSES

SELECT @OpenTranCount = SUM(OPEN_TRAN) FROM MASTER..SYSPROCESSES

SELECT @BlockedCount=COUNT(*),

@ExceptionBlockedCount = SUM(CASE WHEN WAITTIME>@MaxBlockWaitTime THEN 1 ELSE 0 END)

FROM MASTER..SYSPROCESSES

WHERE BLOCKED<>0

IF @ExceptionBlockedCount>0

BEGIN

–Failure if blocked transactions are present that exceeded Timeout

SET @StatusCode=3

SET @StatusMessage = CAST(@ExceptionBlockedCount AS VARCHAR(255)) + ‘ Blocked transactions (with wait time > specified timeout) are present.’

END

ELSE

BEGIN

IF @BlockedCount>0

BEGIN

–Warn regarding blocked transactions

SET @StatusCode=2

SET @StatusMessage = CAST(@BlockedCount AS VARCHAR(255)) + ‘ Blocked transactions were present.’

END

ELSE

BEGIN

–OK

SET @StatusCode=1

SET @StatusMessage = ‘OK.’

END

END

–Return Key counters

DECLARE @Counters TABLE (Counter VARCHAR(50), [Value] INT, OrderBy TINYINT)

INSERT INTO @Counters (Counter, [Value], OrderBy) VALUES(‘# Conns’, COALESCE(@ConnectionCount,0), 1)

INSERT INTO @Counters (Counter, [Value], OrderBy) VALUES(‘# Open Trans’, COALESCE(@OpenTranCount,0), 2)

INSERT INTO @Counters (Counter, [Value], OrderBy) VALUES(‘# Blocked’, COALESCE(@BlockedCount,0), 3)

INSERT INTO @Counters (Counter, [Value], OrderBy) VALUES(‘# Block Exceptions’, COALESCE(@ExceptionBlockedCount,0), 4)

SELECT COUNTER, [Value]

FROM @Counters

ORDER BY OrderBy

END

GO

— ==========================================================

— HOW TO RUN THE PROCEDURE

— ==========================================================

DECLARE @SCode TINYINT,

@SMessage VARCHAR(8000)

EXECUTE [dbo].[SQLActivityMonitor]

@MaxBlockWaitTime = ‘15000’,

@StatusCode = @SCode OUTPUT,

@StatusMessage = @SMessage OUTPUT

SELECT StatusCode = @SCode,StatusMessage = @SMessage

GO

The OutPut Values can be Retrived in FrontEnd application like Powershell,C# etc.

Below is a sample for using the above Procedure and Retrieving the values in Powershell.

Note: Change the ServerName,DatabaseName and MBwaittime as per your convinience.

Also make a note that the MBWaittime is considered in terms of MilliSeconds.

–POWERSHELL SCRIPT TO MONITOR SQL Database.

$ServerName = ‘YOURSERVERNAME’

$DatabaseName = ‘YourDatabaseName’

$Query = ‘SQLActivityMonitor’

$MBWaittime = ‘YourDesiredBlockTime’

$connString = “Server=$serverName;Database=$databaseName;Integrated Security=SSPI;”

$conn = new-object System.Data.SqlClient.SqlConnection $connString

$conn.Open()

$cmd = new-object System.Data.SqlClient.SqlCommand(“$Query”, $conn)

$cmd.CommandType = [System.Data.CommandType]”StoredProcedure”

$cmd.Parameters.Add(“@MaxBlockWaitTime”, [System.Data.SqlDbType]”Int”)

$cmd.Parameters[“@MaxBlockWaitTime”].Value = $MBWaittime

$cmd.Parameters.Add(“@StatusCode”, [System.Data.SqlDbType]”TinyInt”)

$cmd.Parameters[“@StatusCode”].Direction = [System.Data.ParameterDirection]”Output”

$cmd.Parameters.Add(“@StatusMessage”, [System.Data.SqlDbType]”Varchar”,8000)

$cmd.Parameters[“@StatusMessage”].Direction = [System.Data.ParameterDirection]”Output”

$cmd.ExecuteNonQuery()

$conn.Close()

$cmd.Parameters[“@StatusCode”].Value

$cmd.Parameters[“@StatusMessage”].Value

SQL Query to Delete the OLD Back up files based on No of Days

Here is a small SQL Script developed to delete the OLD Backup files based on the Date.

All you need to do is just check how many days old files should be deleted and provide the same in

Select statement after get date.

SET NOCOUNT ON

GO

sp_configure ‘show advanced options’, 1

GO

RECONFIGURE

GO

sp_configure ‘xp_cmdshell’, 1

GO

PRINT ‘Running reconfigure’

RECONFIGURE

GO

DECLARE @rc INT

DECLARE @cmd VARCHAR(1000)

DECLARE @cursor CURSOR

DECLARE @physicaldevicename VARCHAR(1000)

DECLARE @Err VARCHAR(1000)

SET @cursor = CURSOR FOR SELECT b.physical_device_name AS physicaldevicename FROM msdb..backupset AS a

inner join msdb..backupmediafamily as b on a.media_set_id = b.media_set_id

WHERE a.backup_start_date

< GETDATE()-5 ORDER BY a.backup_start_date,a.database_name

OPEN @cursor

WHILE 1=1

BEGIN

FETCH FROM @cursor INTO @physicaldevicename

IF @@fetch_status <> 0

BEGIN

SET @Err = @@fetch_status

PRINT @Err

BREAK

END

ELSE

BEGIN

SET @cmd = ‘del ‘ + @physicaldevicename

PRINT ‘Starting deleting the backup file ‘ + @cmd

EXEC @rc = master.dbo.xp_cmdshell @cmd

IF @rc <> 0

BEGIN

PRINT ‘Backup File ‘ + @physicaldevicename + ‘ was not deleted’

END

ELSE

BEGIN

PRINT ‘Backup File ‘ + @physicaldevicename + ‘ deleted successfully at timestamp ‘+ CONVERT(VARCHAR,GETDATE(),109)

END

END

END

CLOSE @cursor

DEALLOCATE @cursor

GO

sp_configure ‘xp_cmdshell’, 0

GO

RECONFIGURE

GO

sp_configure ‘show advanced options’, 0

GO

PRINT ‘Running reconfigure’

RECONFIGURE

GO

SET NOCOUNT OFF

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;