Oracle DBA Daily Experiences

Using the following SQL statement, it will list triggers on database. It also prints trigger name, table the trigger is on, is it a insert, update and delete triggering event, after and instead of trigger, if the trigger is enabled or disabled

SELECT trigger_name = name
, trigger_owner = USER_NAME(uid)
, table_name = OBJECT_NAME(parent_obj)
, isinsert = OBJECTPROPERTY( id, ‘ExecIsInsertTrigger’)
, isupdate = OBJECTPROPERTY( id, ‘ExecIsUpdateTrigger’)
, isdelete = OBJECTPROPERTY( id, ‘ExecIsDeleteTrigger’)
, isafter = OBJECTPROPERTY( id, ‘ExecIsAfterTrigger’)
, isinsteadof = OBJECTPROPERTY( id, ‘ExecIsInsteadOfTrigger’)
, status = CASE OBJECTPROPERTY(id, ‘ExecIsTriggerDisabled’) WHEN 1 THEN ‘Disabled’ ELSE ‘Enabled’ END
FROM sysobjects
WHERE type = ‘TR’

Output:
tr_test1_insert dbo test1 1 0 0 1 0 Enabled

Run the following SQL to find permissions granted on users on the database

select
case when p.protecttype = 205 then 'GRANT'
when p.protecttype = 206 then 'DENY'
else 'unknown'
end + ' ' +
case when p.action = 193 then 'SELECT'
when p.action = 197 then 'UPDATE'
when p.action = 195 then 'INSERT'
when p.action = 196 then 'DELETE'
when p.action = 224 then 'EXECUTE'
else 'unknown'
end + ' ON ' + objectowner.name + '.' + o.NAME + ' TO ' + u.name
from sysusers u
inner join sysprotects p on u.uid = p.uid
inner join sysobjects o on o.id = p.id
inner join sysusers objectowner on o.uid = objectowner.uid
WHERE u.name = 'username'
order by o.name, u.name

In SQLServer TRUNCATE TABLE can be rolled back when within a transaction. TRUNCATE still doesn’t log each row when it’s deleting each row and it doesn’t call DELETE trigger during truncate.

Example:
BEGIN TRANSACTION
SELECT count(*) FROM test
TRUNCATE TABLE test
SELECT count(*) FROM test
ROLLBACK TRAN
SELECT count(*) FROM test

Output:
———–
2

(1 row(s) affected)

———–
0

(1 row(s) affected)

———–
2

(1 row(s) affected)

Using the following T-SQL one can create differential backup or make it part of scheduled job.

declare @datetimestamp varchar(20)
declare @filename nvarchar(100)
declare @name nvarchar(100)

SELECT @datetimestamp = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20), GETDATE(), 120), '-', ''), ' ', '_'), ':', '')

SELECT @filename = 'C:\testDBBackup\testDBbdiff' + @datetimestamp + '.bak'
SELECT @name = 'testDB Diff Backup ' + @datetimestamp

BACKUP DATABASE testDB
TO DISK = @filename
WITH DIFFERENTIAL,INIT, NAME = @name

By executing the store procedure sp_spaceused ‘<table>’ one can find the space used by a table. The store procedure returns amount of space used by data, index and unused space. Here is the documentation on sp_spaceused from Microsoft.

Using the following link you can use the store proc below to return for all tables in a database http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx.

Using the SQL below one can list SQL Server tables

Use testDB
SELECT table_name FROM INFORMATION_SCHEMA.TABLES;

table_name
———–
ExceptionLog
Employee
…

In Query analyzer

– prints job name, step name, start time and end time of each step in the job and sorts by latest on the top
– if jh.sql_message_id is 0 then that step failed but that status is set set at the overall job outcome
SELECT j.Name, jh.Step_name,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/)
AS Start_DateTime,
CONVERT(DATETIME, RTRIM(jh.run_date)) + ((jh.run_time/10000 * 3600) + ((jh.run_time%10000)/100*60) +
(jh.run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((jh.run_duration/10000 * 3600) + ((jh.run_duration%10000)/100*60) + (jh.run_duration%10000)%100
/*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime
from msdb..sysjobhistory jh, msdb..sysjobs j
where jh.job_id=j.job_id
and j.name = ‘Job_name’
ORDER BY run_date desc, run_time desc

Sample Output: