Saturday, September 19, 2009

Get database status

SELECT DATABASEPROPERTYEX(db_name(), 'Status')

Return value:

ONLINE = Database is available for query.
OFFLINE = Database was explicitly taken offline.
RESTORING = Database is being restored.
RECOVERING = Database is recovering and not yet ready for queries.
SUSPECT = Database did not recover.
EMERGENCY = Database is in an emergency, read-only state. Access is restricted to sysadmin members

Tuesday, September 15, 2009

Get recovery model for the database.

SELECT DATABASEPROPERTYEX(db_name(), 'Recovery')
Return value:
FULL = Full recovery model
BULK_LOGGED = Bulk logged model
SIMPLE = Simple recovery model
Base data type: nvarchar(128)

Wednesday, September 9, 2009

Get all encrypted procedures


SELECT OBJECT_NAME(id) as ObjectName
FROM sys.syscomments
WHERE encrypted = 1

Encrypt stored procedure/ view/ function


CREATE PROCEDURE YourProcedureName
WITH ENCRYPTION
AS

CREATE VIEW YourViewName
WITH ENCRYPTION
AS

CREATE FUNCTION YourFunctionName()
RETURNS INT
WITH ENCRYPTION
AS

Thursday, September 3, 2009

Microsoft SharePoint Designer Team Blog : Locking Down SharePoint Designer

I'm reading Microsoft SharePoint Designer Team Blog : Locking Down SharePoint Designer

ENABLE/DISABLE

Change status of constraint or primary key in a table


ALTER TABLE DEPT
DISABLE CONSTRAINT DEPT_PRIMARY_KEY CASCADE;

Get running cursors and their queries


SELECT NVL(USERNAME,'SYSTEM PROC') USER_NAME, t2.SID,
SQL_TEXT
FROM v$open_cursor t1, v$session t2
WHERE t2.SQL_ADDRESS = t1.ADDRESS
AND t2.SQL_HASH_VALUE = t1.HASH_VALUE
ORDER BY 1

Check which objects modified recently


SELECT OBJECT_NAME,
OBJECT_TYPE,
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATED_DATE,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') LAST_MODIFIED_DATE,
STATUS
FROM USER_OBJECTS
WHERE SYSDATE - LAST_DDL_TIME < 1000;

CREATE FUNCTION

Create a function to get account name by account id.


CREATE OR REPLACE FUNCTION fnGetAccountNameByID(vACCOUNT_ID IN NUMBER)
RETURN VARCHAR2 IS vACCOUNT_NAME VARCHAR2(255);
BEGIN
vACCOUNT_NAME := '';
SELECT ACCOUNT_NAME INTO vACCOUNT_NAME
FROM ACCOUNT
WHERE ACCOUNT_ID = vACCOUNT_ID;

RETURN vACCOUNT_NAME;

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END fnGetAccountNameByID;
/

Oracle Query