Some queries to use less the SQL Server Management Studio

Here I bring you some queries that will help you in the daily tasks that a database administrator does.

See what query is blocking other query:

SP_WHO 

You can see more detailed information. You could see ECID, CPUtime, Disk, LastBatch, ProgramName, SPID

SP_WHO2

Password Change Date is more then 90 days

SELECT LOGINPROPERTY([NAME], 'PasswordLastSetTime') AS 'PasswordChanged' 
FROM SYS.SQL_LOGINS WHERE LOGINPROPERTY([NAME], 'PasswordLastSetTime') < DATEADD(dd, -90, GETDATE())

See all logins

SELECT NAME
	  ,CASE WHEN IS_DISABLED = 0 THEN 'ACTIVE' ELSE 'INACTIVE' END AS 		   STATUS
	  ,TYPE_DESC
	  ,DEFAULT_DATABASE_NAME
	  ,CASE WHEN IS_EXPIRATION_CHECKED = 1 THEN 'YES' ELSE 'NO' END AS STATUS
	  ,CREATE_DATE
	  ,MODIFY_DATE
FROM SYS.SQL_LOGINS

Objects with their schema and type

SELECT O.NAME 'OBJECT'
	  ,S.NAME 'SCHEMA'
	  ,O.TYPE_DESC 'TYPE'
FROM SYS.ALL_OBJECTS O
INNER JOIN SYS.SCHEMAS S ON S.SCHEMA_ID = O.SCHEMA_ID
--WHERE DB.NAME = ''
ORDER BY O.NAME 

--INDEXES LIST
SELECT I.NAME
	  ,I.TYPE_DESC
	  ,CASE WHEN IS_UNIQUE = 1 THEN 'YES' ELSE 'NO' END AS 'UNIQUE'
	  ,CASE WHEN IS_PRIMARY_KEY = 1 THEN 'YES' ELSE 'NO' END AS 'PRIMARY_KEY'
	  ,CASE WHEN IS_UNIQUE_CONSTRAINT = 1 THEN 'YES' ELSE 'NO' END AS 'UNIQUE_CONSTRAINT'
FROM SYS.INDEXES I 

Used index

SELECT 
	db_name(ius.database_id)  'DATABASE_NAME',
	t.NAME   'TABLE_NAME',
	i.NAME   'INDEX_NAME',
	i.type_desc 'INDEX_TYPE' ,
	ius.user_seeks,
	ius.user_scans,
	user_lookups,
	ius.user_seeks + ius.user_scans + ius.user_lookups AS 'NUMBER_TIME_ACCESS'
FROM sys.dm_db_index_usage_stats ius
INNER JOIN sys.indexes i ON i.OBJECT_ID = ius.OBJECT_ID AND i.index_id = ius.index_id
INNER JOIN sys.tables t ON t.OBJECT_ID = i.object_id
--WHERE database_id = DB_ID('YOUR_DATABASE_HERE')
ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC

Index Fragmentation

SELECT S.NAME 'SCHEMA'
	  ,T.NAME 'TABLE'
	  ,I.NAME 'INDEX'
	  ,DDIPS.avg_fragmentation_in_percent 'FRAGMENTATION_PERCENTAGE'
	  ,DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID() 
and I.name is not null 
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc

If the external fragmentation percentage it's major of 10 you should to Reorganize.

If the internal fragmentation percentage it's minor of 75 you should to Reorganize.

If the external fragmentation percentage it's major of 15 you should to Rebuild.

If the internal fragmentation percentage it's minor of 60 you should to Rebuild.

Kill a process

Kill SP_ID

Ex.: Kill 55

Note: All the SP_ID minor of 50 and inclusive are process from the system.

Jobs information

SELECT j.name JobName
	   ,CASE WHEN enabled = 0 THEN 'ACTIVE' ELSE 'INACTIVE' END [Status]
	   ,Description
	   ,jh.Step_name
	   ,jh.Run_time 
	   ,jh.run_duration Duration
	   ,js.schedule_id
	   ,jh.Run_Status [Status]
	   ,j.Date_created 
	   ,j.Date_modified
FROM [dbo].[sysjobs] j
INNER JOIN [dbo].[sysjobhistory] jh on j.job_id = jh.job_id
INNER JOIN [dbo].[sysjobschedules] js on js.job_id = j.job_id
where j.name like '%%' --job name here

Backups information

SELECT bf.logical_name
	  ,bf.file_size
	  ,bf.physical_drive
	  ,bf.physical_name
	  ,bs.[name] 
	  ,bs.[user_name]
	  ,bs.software_major_version
	  ,bs.software_minor_version
	  ,bs.first_lsn
	  ,bs.last_lsn
	  ,bs.checkpoint_lsn
	  ,BMS.software_name
	  ,bs.[backup_start_date]
	  ,bs.[backup_finish_date]
FROM [dbo].[backupfilegroup]  bfg
INNER JOIN [dbo].[backupfile] bf on bf.backup_set_id = bfg.backup_set_id
INNER JOIN [dbo].[backupset] bs on bs.backup_set_id = bfg.backup_set_id
INNER JOIN [dbo].[backupmediaset] bms on bms.media_set_id = bs.media_set_id

To be continued...