Here is a list of Transact SQL Queries, which we need in our day to day database administration activity.

1. COLLECT SQL SERVER DETAILS 

SELECT

SERVERPROPERTY('MachineName') as 'Machine Name',

SERVERPROPERTY('ServerName') as 'Server Name',

SERVERPROPERTY('ProductLevel') as 'Product Level',

SERVERPROPERTY('ProductVersion') as 'Product Version',

SERVERPROPERTY('collation') as 'Server Collation',

SERVERPROPERTY('edition') as 'SQL Server Edition',

SERVERPROPERTY('InstanceName') as 'Instance Name',

CASE SERVERPROPERTY('IsClustered')

WHEN 0 THEN 'No'

WHEN 1 THEN 'Yes'

END as 'Is it clustered?',

CASE SERVERPROPERTY('IsFullTextInstalled')

WHEN 0 THEN 'No'

WHEN 1 THEN 'Yes'

END as 'Full text engine installed?',

CASE SERVERPROPERTY('IsIntegratedSecurityOnly')

WHEN 0 THEN 'Mixed'

WHEN 1 THEN 'Integrated security only'

END as 'Security mode',

SERVERPROPERTY('LicenseType') as 'License Type',

SERVERPROPERTY('NumLicenses') as 'NumLicenses'

GO 

2. COLLECT SQL SERVER DETAILS 

master..xp_msver Language, Platform,CompanyName,FileDescription,WindowsVersion,ProcessorCount,ProcessorActiveMask,ProcessorType,PhysicalMemory

GO 

3. SP_CONFIGURE 

EXEC sp_configure 'show advanced options', 1

RECONFIGURE WITH OVERRIDE

EXEC sp_configure

EXEC sp_configure 'show advanced options', 0

RECONFIGURE WITH OVERRIDE

GO 

4. GET ALL THE DATABASE PLACEMENT DETAILS 

EXEC master..sp_helpdb

GO 

5. XP_REGREAD 

EXEC master..xp_regread 'HKEY_LOCAL_MACHINE\',

'SOFTWARE\Microsoft\MSSQLServer\Setup',

'SQLPath'

GO 

6. FINDING DRIVE SPACE IN THE SERVER 

EXEC master..xp_fixeddrives

GO 

7.) FINDING WHEN WAS LAST BACK TAKEN AND LOCATION 

 SELECT A.database_name as 'DBName',

A.backup_finish_date as 'Backup Finished',

B.physical_device_name as 'Backup Filename'

FROM msdb.dbo.backupset A,

msdb.dbo.backupmediafamily B,

(SELECT database_name,

MAX(backup_finish_date) as 'maxfinishdate'

FROM msdb.dbo.backupset

WHERE Type = 'D'

GROUP BY database_name) C

WHERE A.media_set_id = B.media_set_id AND

A.backup_finish_date = C.maxfinishdate AND

A.type = 'D'

ORDER BY DBName

GO 

8. FINDING SQL SERVER VERSION AND DETAILS 

select @@VERSION

GO 

9. FINDING SUB DIRECTORIES 

EXEC master..xp_dirtree 'C:\Program Files\Microsoft SQL Server\MSSQL'

GO 

10. READ THE LOG THROUGH QUERY WINDOW 

EXEC master..xp_readerrorlog

GO 

11. FINDING THE DRIVE SPACE 

EXEC xp_availablemedia

GO 

12. FINDING THE TAPE DEVICE 

EXEC xp_get_tape_devices

GO 

13. FINDING THE FILE DETAILS 

xp_getfiledetails 'C:\NARESH\SQL Server\SITELITE\SP_PH_CREATE_DATABASE_USAGE_SCRIPT.SQL'

GO 

14. IP CONFIGURATION 
exec master..xp_cmdshell 'regedit /E "C:\MSSQL.reg" "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer"' 

exec master..xp_cmdshell 'regedit /E "C:\ODBC.reg" "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI"' 

exec master..xp_cmdshell 'ipconfig /all'

GO