TOADSoft.com - Home of the World's #1 Tool for Database Application Developers and DBAs!

When working with dbs, I think you should have some useful tools to be able to accelerate your db tasks. I often use Toad tools in this site
TOADSoft.com - Home of the World's #1 Tool for Database Application Developers and DBAs!
At the moment, I am using Toad for Oracle & Toad for MySQL

Oracle PL/SQL Version Controlling

--Firstly, create a table to store change history

CREATE TABLE DBChangeHistory AS 
SELECT SYSDATE CHANGE_DATE, ALL_SOURCE.*
FROM   ALL_SOURCE 
WHERE 1=2;

--Secondly, create a trigger to store the code when it's changed
--You can modify this code to store Version number
CREATE OR REPLACE TRIGGER tgTrackDBChange
AFTER CREATE ON SCOTT.SCHEMA
DECLARE
BEGIN
  IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
                           'PACKAGE',   'PACKAGE BODY',
                           'TYPE',      'TYPE BODY')
  THEN     
     INSERT INTO DBChangeHistory
     SELECT sysdate, all_source.* 
  FROM ALL_SOURCE
     WHERE  TYPE = ORA_DICT_OBJ_TYPE
  AND  NAME = ORA_DICT_OBJ_NAME;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
       --Raise error here;
END;

Microsoft Releases SQL Azure Database CTP

Microsoft Releases SQL Azure Database CTP

Enterprise App Developers Use Insecure Data - InternetNews.com

Enterprise App Developers Use Insecure Data - InternetNews.com

Business Analysts: Role Changes Require New Skill Sets

I have read this article Business Analysts: Role Changes Require New Skill Sets on CIO.com and would like to share with you.
I think some DBAs are also business analysts like me.

Find a text inside stored procedure, trigger, view, etc.

Sometimes, you need to find a specific text in your stored procedures, this script may be useful to help you.

SELECT DISTINCT t2.name [Object Name],
    CASE t2.type
        WHEN'U' THEN 'User Table'
        WHEN'S' THEN 'System Table'
        WHEN 'V' THEN 'View'
        WHEN 'TR' THEN 'Trigger'
        WHEN 'P' THEN 'Stored Procedure'
        WHEN 'C' THEN 'Constraint - Check'
        WHEN 'D' THEN 'Default'
        WHEN 'K' THEN 'Primary Key'
        WHEN 'F' THEN 'Foreign Key'
        WHEN 'L' THEN 'Log'
        WHEN 'R' THEN 'Rule'
        WHEN 'RF' THEN 'Replication Filter stp'
        ELSE 'Other: ''' + t2.type+ ''''
    END [Object Type],
 t2.crdate [Created Date]
FROM syscomments t1
    INNER JOIN sysobjects t2 ON t1.id = t2.id
WHERE t1.[text] LIKE '%Your text need searching%'

Find a column in database

SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM ALL_TAB_COLS
WHERE COLUMN_NAME = 'Your column name you want to find'
It's simple but very useful for any DBAs.

Read RSS feed by using SQL Server stored procedure

If you want to read RSS Feed by using SQL Server stored procedure, you can use CLR stored procedure as described below:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Xml;
using System.Text;
using System.Xml.XPath;

public partial class StoredProcedures
{
  [Microsoft.SqlServer.Server.SqlProcedure]
  public static void spReadRSSFeed(string strURL)
  {
      XPathDocument doc = new XPathDocument(strURL);
      XPathNavigator nav = doc.CreateNavigator();
      XPathNodeIterator i = nav.Select("//item");
      SqlMetaData[] rss_results = new SqlMetaData[4];
      rss_results[0] = new SqlMetaData("Title", SqlDbType.NVarChar, 250);
      rss_results[1] = new SqlMetaData("Publication Date",
          SqlDbType.DateTime);
      rss_results[2] = new SqlMetaData("Description",
          SqlDbType.NVarChar, 2000);
      rss_results[3] = new SqlMetaData("Link", SqlDbType.NVarChar, 1000);
      SqlDataRecord record = new SqlDataRecord(rss_results);
      SqlPipe sqlpipe = SqlContext.Pipe;
      sqlpipe.SendResultsStart(record);
      while (i.MoveNext())
      {
          record.SetString(0, (string)
              i.Current.Evaluate("string(title[1]/text())"));
          record.SetDateTime(1, DateTime.Parse((string)
              i.Current.Evaluate("string(pubDate[1]/text())")));
          record.SetString(2, (string)
              i.Current.Evaluate("string(description[1]/text())"));
          record.SetString(3, (string)
              i.Current.Evaluate("string(link[1]/text())"));
          sqlpipe.SendResultsRow(record);
      }
      sqlpipe.SendResultsEnd();
  }
};

After building to an assembly named RSSReader, for example, you can deploy it to your SQL Server like this:

CREATE ASSEMBLY [RSSReader]
AUTHORIZATION [dbo]
FROM 'Path to your RSSReader.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
-- Note that you have to use EXTERNAL_ACCESS because we need to access Internet
GO
CREATE PROCEDURE [dbo].[spReadRSSFeed]
@URL [nvarchar](4000)
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [RSSReader].[StoredProcedures].[spReadRSSFeed];

-- Running for testing
spReadRSSFeed 'http://msdn.microsoft.com/sql/rss.xml'

-- Remove them
DROP PROCEDURE spReadRSSFeed
DROP ASSEMBLY RSSReader

SQL Server Tip

Backup your databases every day and make sure that backup job was made successfully and saved to secured places.

Comment on table/column

Description on a table
COMMENT ON TABLE ACCOUNT IS 'Account Information'
Description of a column in table
COMMENT ON COLUMN ACCOUNT.ACCOUNT_NAME IS 'Name of Account'
Drop description, let it blank
COMMENT ON COLUMN ACCOUNT.ACCOUNT_NAME IS ''