Wednesday, July 22, 2009

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%'

Monday, July 20, 2009

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

Friday, July 17, 2009

SQL Server Tip

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

Wednesday, July 15, 2009

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 ''

Tuesday, July 14, 2009

Get all column comments of a table


SELECT *
FROM ALL_COL_COMMENTS
WHERE TABLE_NAME = 'YourTableName'

Delete a table

Be careful to use this query
DROP TABLE YourTable;
Drop a table and related references
DROP TABLE YourTable CASCADE CONSTRAINT;

Get source code/script of a view

You can use this query to get the script of your view quickly.


SELECT TEXT
FROM all_views
WHERE VIEW_NAME = 'Your View Name'

Get objects created/modified recently


SELECT OBJECT_NAME,
TO_CHAR(CREATED, 'DD-Mon-RR HH24:MI') CREATE_TIME,
TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
STATUS
FROM USER_OBJECTS
WHERE SYSDATE - LAST_DDL_TIME < 7

Wednesday, July 8, 2009

SQL Sever queries take most I/O


SELECT TOP 100
(total_logical_reads + total_logical_writes) / qs.execution_count AS average_IO,
(total_logical_reads + total_logical_writes) AS total_IO,
qs.execution_count AS execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS indivudual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_IO DESC;

Queries taking longest elapsed time

Sometimes, you need to determine which queries take a lot of time to run. In this case, you can use this query to check.


SELECT TOP 100 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds,
qs.total_elapsed_time / 1000000.0 AS total_seconds,
qs.execution_count,
SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query,
o.name AS object_name,
DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_id
WHERE qt.dbid = DB_ID()
ORDER BY average_seconds DESC;

Rename a table

Rename EMP to EMPLOYEE:
RENAME EMP TO EMPLOYEE;

Tuesday, July 7, 2009

Check active session

SELECT *
FROM v$session
WHERE STATUS = 'ACTIVE'
AND OSUSER = 'Your OSUSER'

10 Things Small Businesses Must do to Protect & Secure Data

In this podcast, Donna Childs of Prepared Small Business, discusses some best practices to help small businesses keep their information secure, protected, and well-managed.
Read here
Source: Symantec Corporation