SQL Queries - General Admin

SQL Queries - General Admin

---

USE: http://extras.sqlservercentral.com/prettifier/prettifier.aspx
      for t-sql formatting

-- Query to search for a particular data type

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE data_type ='varchar'SELECT * FROM MASTER.INFORMATION_SCHEMA.COLUMNS WHERE data_type ='varchar'

-- Query to create login
USE MASTERGOIF NOT EXISTS (SELECT * FROM sys.syslogins WHERE name = 'svankaya')
    CREATE LOGIN svankaya WITH PASSWORD = 'ch@ng3m3' MUST_CHANGE, CHECK_EXPIRATION = ON, DEFAULT_DATABASE=[master] ;GOIF NOT EXISTS (SELECT * FROM sys.server_role_members drm JOIN sys.server_principals mp
    ON drm.member_principal_id = mp.principal_id
    JOIN sys.server_principals rp
    ON rp.principal_id = drm.role_principal_idWHERE rp.name = 'sysadmin' AND mp.name = 'svankaya')ALTER SERVER ROLE [sysadmin] ADD MEMBER [svankaya]
GO

---

sp_who
sp_who2

SELECT * FROM MASTER..sysprocesses
--where status = 'runnable' --comment this outORDER BY CPU
DESC-- http://whoisactive.comsp_whoisactive  

---

Running Jobs

SELECT sj.name
   , sja.*FROM msdb.dbo.sysjobactivity AS sja
INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
WHERE sja.start_execution_date IS NOT NULL
   AND sja.stop_execution_date IS NULL 

Uncompleted Jobs

SELECT [sjv].[name], [sjv].[description], [sja].[run_requested_date], [sja].[last_executed_step_id]
FROM [msdb].[dbo].[sysjobs_view] sjv
   JOIN [msdb].[dbo].[sysjobactivity] sja ON [sjv].[job_id] = [sja].[job_id]
WHERE [sja].[run_requested_date] IS NOT NULL AND [sja].[stop_execution_date] IS NULL 

-- Orphaned / uncompleted ... how to get only the activity for the current session

SELECT
    job.name,
    job.job_id,
    job.originating_server,
    activity.run_requested_date,
    DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) AS Elapsed
FROM     msdb.dbo.sysjobs_view job
   JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
   JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id
   JOIN (
           SELECT MAX( agent_start_date ) AS max_agent_start_date
           FROM msdb.dbo.syssessions
       ) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date
WHERE run_requested_date IS NOT NULL AND stop_execution_date IS NULL  

-- Help about particular job

EXEC msdb.dbo.sp_help_job @Job_name = '' -- Gives Job details, step details, schedule details, server last run details  

-- To stop job 

EXEC dbo.sp_stop_job ''

---