Backup file detail

RESTORE HEADERONLY FROM DISK = N'E:\SOFTWARES\GnB_EnS.bak'
RESTORE FILELISTONLY FROM DISK = N'E:\SOFTWARES\GnB_EnS.bak'
RESTORE LABELONLY FROM DISK = N'E:\SOFTWARES\GnB_EnS.bak'

Backup file contents

exec xp_objectrecovery_viewcontents
@FileName = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Payroll_201004111708.bak',
@FileNumber = 1,
@Type = 'All'

Server properties

SELECT SERVERPROPERTY('BuildClrVersion') AS BuildClrVersion,
SERVERPROPERTY('Collation') AS Collation,
SERVERPROPERTY('CollationID') AS CollationID,
SERVERPROPERTY('ComparisonStyle') AS ComparisonStyle,
SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS
ComputerNamePhysicalNetBIOS,
SERVERPROPERTY('Edition') AS Edition,
CASE SERVERPROPERTY('EditionID')
WHEN -1253826760 THEN 'Desktop'
WHEN -1592396055 THEN 'Express'
WHEN -1534726760 THEN 'Standard'
WHEN 1333529388 THEN 'Workgroup'
WHEN 1804890536 THEN 'Enterprise'
WHEN -323382091 THEN 'Personal'
WHEN -2117995310 THEN 'Developer'
WHEN 610778273 THEN 'Enterprise Evaluation'
WHEN 1044790755 THEN 'Windows Embedded SQL'
WHEN 4161255391 THEN 'Express with Advanced Services'
ELSE '???'
END AS EditionID,
CASE SERVERPROPERTY('EngineEdition')
WHEN 1 THEN 'Personal-Desktop'
WHEN 2 THEN 'Standard'
WHEN 3 THEN 'Enterprise'
WHEN 4 THEN 'Express'
ELSE '???'
END AS EngineEdition,
SERVERPROPERTY('InstanceName') AS InstanceName,
SERVERPROPERTY('IsClustered') AS IsClustered,
SERVERPROPERTY('IsFullTextInstalled') AS IsFullTextInstalled,
SERVERPROPERTY('IsIntegratedSecurityOnly') AS IsIntegratedSecurityOnly,
SERVERPROPERTY('IsSingleUser') AS IsSingleUser,
SERVERPROPERTY('LCID') AS LCID,
SERVERPROPERTY('LicenseType') AS LicenseType,
SERVERPROPERTY('MachineName') AS MachineName,
SERVERPROPERTY('NumLicenses') AS NumLicenses,
SERVERPROPERTY('ProcessID') AS ProcessID,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ResourceLastUpdateDateTime') AS
ResourceLastUpdateDateTime,
SERVERPROPERTY('ResourceVersion') AS ResourceVersion,
SERVERPROPERTY('ServerName') AS ServerName,
SERVERPROPERTY('SqlCharSet') AS SqlCharSet,
SERVERPROPERTY('SqlCharSetName') AS SqlCharSetName,
SERVERPROPERTY('SqlSortOrder') AS SqlSortOrder,
SERVERPROPERTY('SqlSortOrderName') AS SqlSortOrderName,
SERVERPROPERTY('FilestreamShareName') AS FilestreamShareName,
SERVERPROPERTY('FilestreamConfiguredLevel') AS FilestreamConfiguredLevel,
SERVERPROPERTY('FilestreamEffectiveLevel') AS FilestreamEffectiveLevel

SP_WHO@_DB Procedure

sp_who2_db 'staf'
create PROCEDURE sp_who2_db(@dbname varchar(100))
AS
BEGIN
IF object_id('tab1') IS NOT NULL
DROP TABLE tab1
CREATE TABLE tab1( i1 varchar(1000),i2 varchar(1000),i3 varchar(1000),i4 varchar(1000),
i5 varchar(1000),i6 varchar(1000),i7 varchar(1000),i8 varchar(1000),
i9 varchar(1000),i10 varchar(1000),i11 varchar(1000),
i12 varchar(1000),i13 varchar(1000))

INSERT INTO tab1
EXEC ('sp_who2 ')
SET @dbname = '%' + @dbname +'%'
SELECT * FROM tab1 WHERE i6 LIKE @dbname
END

Longest running query

select top 10 db_name(dbid),
total_worker_time/execution_count as avg_cpu_cost, plan_handle,
execution_count,
(select substring(text, statement_start_offset/2 + 1,
(case when statement_end_offset = -1
then len(convert(varchar(max), text))* 2
else statement_end_offset end – statement_start_offset)/2)
from sys.dm_exec_sql_text(sys.dm_exec_query_stats.sql_handle)) as query_text
from sys.dm_exec_query_stats join sys.sysprocesses
on sys.dm_exec_query_stats.sql_handle = sys.sysprocesses.sql_handle
order by total_worker_time desc

Parameter to query

DECLARE @g int
DECLARE @g1 nvarchar(4000)
SET @g1 = 'SELECT @g=10'
EXEC SP_EXECUTESQL @g1 ,N'@g int out',@g out
PRINT @g

Password encrypt and decrypt of a string

SELECT
encryptbyPASSphrase('key','ABC')
SELECT
CONVERT(VARCHAR(100),DecryptbyPASSphrase('key',0x01000000D5635122845D73E9C224A4B4007A61A9673055D29C966C81))
select
EncryptByPassPhrase('key', 'abc' )
result encrypted
value: 0x01000000674FB952B3F9D575EB4B9E7A22BEC4800F6310C7 F96CA821
select
convert(varchar(100),DecryptByPassPhrase('key', 0x01000000011FFAE33EA305F8DE68453A1CE3425C3F663849D02F21CE))