Tuesday, August 28, 2007

How to convert Vietnamese name to English name?

Sometimes, you want to convert Vietnamese name with Unicode font to English name without accent. How can you do that in T-SQL?
Here's my function to do that:

CREATE FUNCTION dbo.fnConvertToEnglishName(@VNString NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @ENString NVARCHAR(4000)

DECLARE @I INT, @J INT
DECLARE @Array NVARCHAR(1000)

SET @Array = N'àảãáạằẳẵắặăầẩẫấậâa' +
N'èẻẽéẹềểễếệêeeeeeee' +
N'ìỉĩíịiiiiiiiiiiiii' +
N'òỏõóọồổỗốộôờởỡớợơo' +
N'ùủũúụừửữứựưuuuuuuu' +
N'ÈẺẼÉẸỀỂỄẾỆÊEEEEEEE' +
N'ÌỈĨÍỊIIIIIIIIIIIII' +
N'ÒỎÕÓỌỒỔỖỐỘÔỜỞỠỚỢƠO' +
N'ÀẢÃÁẠẰẲẴẮẶĂẦẨẪẤẬÂA' +
N'ÙỦŨÚỤỪỬỮỨỰƯUUUUUUU' +
N'ỳỷỹýỵyyyyyyyyyyyyy' +
N'ỲỶỸÝỴYYYYYYYYYYYYY' +
N'đddddddddddddddddd' +
N'ĐDDDDDDDDDDDDDDDDD'

SET @ENString = @VNString;

SET @I = 1;
SET @J = 1;
WHILE @I <= LEN(@Array) BEGIN IF (@I%18 = 0) BEGIN SET @J = @J + 1 END ELSE BEGIN IF CAST(SUBSTRING(@Array, @I, 1) AS VARBINARY) <> CAST(SUBSTRING(@Array, 18*@J, 1) AS VARBINARY)
SET @ENString = Replace(@ENString COLLATE Latin1_General_BIN,
SUBSTRING(@Array, @I, 1) ,
SUBSTRING(@Array, 18*@J, 1) );
END
SET @I = @I + 1;
END

RETURN @ENString;
END

Sunday, August 26, 2007

How to get source of a stored procedure?

If the stored procedure is not encrypted, you can use this extended stored procedure:
sp_helptext 'StroreProcedureName'
You can find source of a view or a function similarly by using sp_helptext.

How to export a table to XML by T-SQL?

SELECT column list
FROM table list
WHERE filter criteria
FOR XML
RAW
| AUTO
| EXPLICIT [, XMLDATA] [, ELEMENTS] [, BINARY BASE64]

How to export a table to HTML by T-SQL?

EXEC sp_makewebtask @outputfile = 'C:\temp\cust_table.HTML', @query='SELECT CompanyName, CustomerID FROM Northwind..Customers ORDER BY CompanyName' , @lastupdated=0,@resultstitle=' '

Saturday, August 25, 2007

How to find tables related to a column?

You are a DBA for a database, sometimes you need to know tables related to a column.
You can use this query to find that:

If you use Oracle database:

SELECT *
FROM all_tab_cols
WHERE table_name like ‘PartOfTable%’
AND column_name = ‘ColumnName’

If you use SQL Server 2005 database:
SELECT t1.*
FROM sys.columns t1 INNER JOIN
sys.objects t2 ON t1.object_id = t2.object_id
WHERE t1.name = 'ColumnName'

Oracle Query