Tags » SQL

Query to get lastest stored procedure, tables and views

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = ‘P’ //P Specifies Stored Procedure
order by modify_date desc

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = ‘U’ //U Specifies user defined table… 25 more words

SQL

Quick SQL query for complicated leaderboards...

Hmm where to start? I wrote a leaderboard system (front end and backend) since i didn’t see any leaderboard solutions that had all the features I needed. 558 more words

Code

GENERATE CURRENT AUDIT SCRIPT SQL

select 'AUDIT '||m.name||decode(u.name,'PUBLIC',' ',' BY '||u.name)||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,' BY SESSION WHENEVER SUCCESSFUL ',
2,' BY ACCESS WHENEVER SUCCESSFUL ',
10,' BY SESSION WHENEVER NOT SUCCESSFUL ',
11,' BY SESSION ', -- default
20, ' BY ACCESS WHENEVER NOT SUCCESSFUL ',
22, ' BY ACCESS',' /* not possible */ ')||' ;'
"AUDIT STATEMENT"
FROM sys.audit$ a, sys.user$ u, sys.stmt_audit_option_map m
WHERE a.user# = u.user# AND a.option# = m.option#
and bitand(m.property, 1) != 1 and a.proxy# is null
and a.user# <> 0
UNION
select 'AUDIT '||m.name||decode(u1.name,'PUBLIC',' ',' BY '||u1.name)||
' ON BEHALF OF '|| decode(u2.name,'SYS','ANY',u2.name)||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,' WHENEVER SUCCESSFUL ',
2,' WHENEVER SUCCESSFUL ',
10,' WHENEVER NOT SUCCESSFUL ',
11,' ', -- default
20, ' WHENEVER NOT SUCCESSFUL ',
22, ' ',' /* not possible */ ')||';'
"AUDIT STATEMENT"
FROM sys.audit$ a, sys.user$ u1, sys.user$ u2, sys.stmt_audit_option_map m
WHERE a.user# = u2.user# AND a.option# = m.option# and a.proxy# = u1.user#
and bitand(m.property, 1) != 1 and a.proxy# is not null
UNION
select 'AUDIT '||p.name||decode(u.name,'PUBLIC',' ',' BY '||u.name)||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,' BY SESSION WHENEVER SUCCESSFUL ',
2,' BY ACCESS WHENEVER SUCCESSFUL ',
10,' BY SESSION WHENEVER NOT SUCCESSFUL ',
11,' BY SESSION ', -- default
20, ' BY ACCESS WHENEVER NOT SUCCESSFUL ',
22, ' BY ACCESS',' /* not possible */ ')||' ;'
"AUDIT STATEMENT"
FROM sys.audit$ a, sys.user$ u, sys.system_privilege_map p
WHERE a.user# = u.user# AND a.option# = -p.privilege
and bitand(p.property, 1) != 1 and a.proxy# is null
and a.user# <> 0
UNION
select 'AUDIT '||p.name||decode(u1.name,'PUBLIC',' ',' BY '||u1.name)||
' ON BEHALF OF '|| decode(u2.name,'SYS','ANY',u2.name)||
decode(nvl(a.success,0) + (10 * nvl(a.failure,0)),
1,' WHENEVER SUCCESSFUL ',
2,' WHENEVER SUCCESSFUL ',
10,' WHENEVER NOT SUCCESSFUL ',
11,' ', -- default
20, ' WHENEVER NOT SUCCESSFUL ',
22, ' ',' /* not possible */ ')||';'
"AUDIT STATEMENT"
FROM sys.audit$ a, sys.user$ u1, sys.user$ u2, sys.system_privilege_map p
WHERE a.user# = u2.user# AND a.option# = -p.privilege and a.proxy# = u1.user#
and bitand(p.property, 1) != 1 and a.proxy# is not null;
ORACLE

TSQL to Rollup multiple rows into single row and column

In my team, we mange multiple Applications and Servers. As part of Application migrations, Server consolidations, failovers application Server mapping will always be changing. I always depend on Application Server mapping during analysis but my major challenge was with multiple rows for each app in traditional way. 419 more words

SQL

JPMorgan and Other Banks Struck by Cyberattack

A number of United States banks, including JPMorgan Chase and at least four others, were struck by hackers in a series of coordinated attacks this month, according to four people briefed on a continuing investigation into the crimes. 52 more words

Jay Leiderman

Troubleshooting : Error - Incorrect syntax near 'GO'

Sometimes we are very comfortable and used to with certain things that if they change, we become nervous and uneasy. I have a lovely daughter and she always greets me when I come back home from office. 269 more words

SQL

SQL SERVER - Detecting Corruption with Suspect Pages Table - Notes from the Field #043

: This is a new episode of Notes from the Fields series. I often hear about database corruption and I like to stay away from the problems related to corruption. 530 more words

SQL Authority