duplicate or clone MySQL database table

Many will facing issue when try to duplicate or clone database table in MySQL.
Below is the script and it's tested working well.

New table: tbltransaction_new
Existing table: tbltransaction

CREATE TABLE tbltransaction_new LIKE tbltransaction;
INSERT tbltransaction_new SELECT * FROM tbltransaction;

SQL query and optimization

I've run a simple test on sql query involve 2 tables, tblEmail & tblEmailPromotion

Table columns:

tblEmail (email varchar(255), IsDeleted int)

tblEmailPromotion (email varchar(255), PromotionID int)

Both tables without index

SELECT TOP (90) e.Email FROM tblEmail e

LEFT OUTER JOIN tblPromotionEmail pe ON e.Email = pe.Email AND pe.PromotionID = 6

WHERE pe.PromotionID IS NULL

AND e.IsDeleted = 0

AND e.Email LIKE '%hotmail%'

ORDER BY e.Email

It takes about 2:43 (2 minutes 43 seconds) to get the result

Change the question from "LEFT OUTER JOIN" to "IN" become

SELECT TOP (90) e.Email FROM tblEmail e

WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE pe.PromotionID = 6)

AND e.IsDeleted = 0

AND e.Email LIKE '%hotmail%'

ORDER BY e.Email

Guest what? great improvement. Takes 1:07 (1 minute 7 seconds) to load the result.

Now, we index table tblEmail column Email (Unique)

SELECT TOP (90) e.Email FROM tblEmail e

LEFT OUTER JOIN tblPromotionEmail pe ON e.Email = pe.Email AND pe.PromotionID = 6

WHERE pe.PromotionID IS NULL

AND e.IsDeleted = 0

AND e.Email LIKE '%hotmail%'

ORDER BY e.Email

It takes about 1:22 (1 minute 22 seconds) to get the result

Now, we run the 2nd query:

SELECT TOP (90) e.Email FROM tblEmail e

WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE pe.PromotionID = 6)

AND e.IsDeleted = 0

AND e.Email LIKE '%hotmail%'

ORDER BY e.Email

It only takes 27 Seconds

To make the 2nd query better:

SELECT TOP (90) e.Email FROM tblEmail e

WHERE e.Email NOT IN (SELECT Email FROM tblPromotionEmail pe WHERE pe.PromotionID = 6 AND pe.Email LIKE '%hotmail%')

AND e.IsDeleted = 0

AND e.Email LIKE '%hotmail%'

ORDER BY e.Email

This will give 15 seconds.

Summary:

We need to choose the query wisely.

Index will help to improve data retrieving process but must be careful with indexes. Wrong indexes may cause you more problem.

Searching word in Stored Procedure

Sometime it's very useful if we can search word in Stored Procedure for debugging purposes or etc. Use below script to search with stored procedure using table called "tblLookup"

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%tblLookup%'
AND ROUTINE_TYPE='PROCEDURE'
ORDER BY ROUTINE_NAME ASC

Invalid use of 'EXECUTE' within a function

I've tried to create a MSSQL "User Defined Functions" with execution of dynamic query (return a final value) and hit error message: "Invalid use of 'EXECUTE' within a function"

The reason is MSSQL not allow execution of dynamic query in "User Defined Functions". Because of this, i need to change from user "User Defined Functions" to "stored procedure" SP by using sp_executesql.

Below show example the Stored Procedure that i use to overcome my problem.

I need my SP to return a calculated value.

CREATE PROCEDURE dbo.psp_GetOCPrice
(
@strSKUNo VARCHAR(50),
@BOMT NUMERIC(9,2),
@OtherCharges NUMERIC(9,2) OUTPUT
)
AS
BEGIN

DECLARE @Count INT,
@Max INT,
@Cost NUMERIC(9,2),
@SQL NVARCHAR(4000),
@Formula NVARCHAR(1000),
@othMainIdn INT,
@othIdn INT


DECLARE @tblOthCharges TABLE
(
ID INT IDENTITY(1,1),
SKUNo VARCHAR(50),
othIdn INT,
othMainIdn INT,
Formula VARCHAR(1000),
Cost NUMERIC(9,2)
)

INSERT INTO @tblOthCharges(SKUNo, othIdn, othMainIdn, Formula)
SELECT sm.SKUNo, sm.pid, sm.ItemGroup,ISNULL(sm.Formula,0) Formula
FROM tblSKUMat sm
WHERE SKUNo = @strSKUNo
AND sm.othCharFlag = 1 ORDER BY sm.seqID ASC

SET @BOMT = ISNULL(@BOMT,0)

SET @Max = (SELECT COUNT(ID) FROM @tblOthCharges)

SET @Count = 1
SET @Cost = 0

SET @SQL = ''

SET @SQL = @SQL + ' DECLARE @BOM NUMERIC(9,2), ' + char(13)
SET @SQL = @SQL + ' @Cost NUMERIC(9,2) ' + char(13)
SET @SQL = @SQL + ' SET @BOM = ' + CONVERT(VARCHAR,@BOMT) + ' ' + char(13)
SET @SQL = @SQL + ' SET @rOtherCharges = 0 ' + char(13)

WHILE @Count <= @Max BEGIN SELECT @Formula = Formula, @othMainIdn = othMainIdn, @othIdn = othIdn FROM @tblOthCharges WHERE ID = @Count

SET @SQL = @SQL + ' SELECT @Cost = ' + CONVERT(VARCHAR,@Formula) + char(13)

SET @SQL = @SQL + ' DECLARE @v' + CONVERT(VARCHAR,@othMainIdn) + '_value NUMERIC(9,2) '

SET @SQL = @SQL + char(13)

SET @SQL = @SQL + ' SET @v' + CONVERT(VARCHAR,@othMainIdn) + '_value = ISNULL(@Cost,0) '

SET @SQL = @SQL + char(13)

SET @SQL = @SQL + ' SET @rOtherCharges = @rOtherCharges + ISNULL(@Cost,0) ' + char(13)

SET @Count = @Count + 1
END

DECLARE @ParmDefinition NVARCHAR(500);

SET @ParmDefinition = N'@rOtherCharges NUMERIC(9,2) OUTPUT';

EXECUTE sp_executesql
@SQL,
@ParmDefinition,
@rOtherCharges = @OtherCharges OUTPUT;

END

GO

i need to use dynamic query because the field "Formula" is mathematics expression where involve calculation using value from parameter.
By using sp_executesql, the value for @rOtherCharges will be assigned to @OtherCharges OUTPUT.

To call this SP, simply call execution

Declare @OtherCharges
Execute dbo.psp_GetOCPrice 'XIN200805010001', 2000.20, @OtherCharges NUMERIC(9,2)

Done!

Create View using Stored Procedure dynamically

We can create view using stored procedure dynamically.

Please find my sample below:
I want to create a view name v_rpt + @Type and @Type as parameter. If the view already exists, it will be drop and recreate a new one.

We will need to execute @SQL twice

• execution 1: check whether the view exists, drop it if exists
• execution 2: create a new view

if we put both in the same query, it will hit error "'CREATE VIEW' must be the first statement in a query batch."


CREATE PROCEDURE [dbo].psp_Generate_view
(
@Type VARCHAR(255)
)
AS

BEGIN

SET NOCOUNT ON

DECLARE @SQL VARCHAR(8000)

SET @SQL = ''
SET @SQL = @SQL + 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = OBJECT_ID(N''[dbo].[v_rpt' + CONVERT( VARCHAR, @Type) + ']'') AND OBJECTPROPERTY(id, N''IsView'') = 1) ' + char(13)
SET @SQL = @SQL + 'DROP VIEW [dbo].[v_rpt' + CONVERT( VARCHAR, @Type) + '] ' + char(13)

EXECUTE (@SQL)

SET @SQL = ''
SET @SQL = @SQL + 'CREATE VIEW dbo.v_rpt' + CONVERT( VARCHAR, @Type) + ' AS ' + char(13)
SET @SQL = @SQL + 'SELECT DISTINCT '
SET @SQL = @SQL + 's.SKUNo, '
SET @SQL = @SQL + 's.Model, '
SET @SQL = @SQL + 'lk1.LookupDesc [Region], '
SET @SQL = @SQL + 'lk2.LookupDesc [Country], '
SET @SQL = @SQL + 'CASE s.Status '
SET @SQL = @SQL + ' WHEN 1 THEN ''Saved'' '
SET @SQL = @SQL + ' WHEN 2 THEN ''Rejected'''
SET @SQL = @SQL + ' WHEN 3 THEN ''Approved'''
SET @SQL = @SQL + 'END AS Status, '
SET @SQL = @SQL + 's.Remark, '
SET @SQL = @SQL + 'u1.FullName [Creator], '
SET @SQL = @SQL + 's.CreDte [Date of Creation], '
SET @SQL = @SQL + 'u2.FullName [UpdatedBy], '
SET @SQL = @SQL + 's.LastUpdDte [Updated On], '
SET @SQL = @SQL + 'FROM tblSKU s '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblUser u1 ON s.OwnerID = u1.UserID '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblUser u2 ON s.LastUserID = u2.UserID '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblLookup lk1 ON s.Region = lk1.RefIdn AND lk1.GroupIdn = 1 '
SET @SQL = @SQL + 'LEFT OUTER JOIN tblLookup lk2 ON s.CTY = lk2.RefIdn AND lk2.GroupIdn = 2 '
SET @SQL = @SQL + 'WHERE '
SET @SQL = @SQL + 's.Type= ' + CONVERT( VARCHAR, @Type) + ' AND '

EXECUTE (@SQL)


SET NOCOUNT OFF
END

Please let me know if you have any comment.

Shrink Database MSSQL

Log file grow until very huge file? Check this step to shrink sql ldf file http://dotnetfish.blogspot.com/2007/12/shrinking-transaction-log.html

Change Table and Stored Procedure Ownership From xxx to DBO

Need to change the ownership for table and stored procedure

Script for change table ownership

DECLARE @old sysname, @new sysname, @sql varchar(1000)

SET @old = 'oldOwner'
SET @new = 'dbo'
SET @sql = ' IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES
WHERE
QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?''
AND TABLE_SCHEMA = ''' + @old + ''' )
EXECUTE sp_changeobjectowner ''?'', ''' + @new + ''''

EXECUTE sp_MSforeachtable @sql

Credit should give to Scott Forsyth

change ownership for stored procedure

DECLARE
@OldOwner sysname,
@NewOwner sysname

SET @OldOwner = 'oldOwner'
SET @NewOwner = 'dbo'

DECLARE CURS CURSOR FOR
SELECT
name
FROM sysobjects
WHERE type = 'p'
AND
uid = (SELECT uid FROM sysusers WHERE name = @OldOwner)
AND
NOT name LIKE 'dt%' FOR READ ONLY

DECLARE @ProcName sysname
OPEN CURS
FETCH CURS INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @@VERSION >= 'Microsoft SQL Server 2005'
BEGIN
EXEC('alter schema ' + @NewOwner + ' transfer ' + @OldOwner + '.' + @ProcName)
exec('alter authorization on ' + @NewOwner + '.' + @ProcName + ' to schema owner')
END
ELSE
EXEC('sp_changeobjectowner ''' + @OldOwner + '.' + @ProcName + ''', ''' + @NewOwner + '''')

FETCH CURS INTO @ProcName
END

CLOSE CURS
DEALLOCATE CURS

Credit should give to Greg Duffield

UPDATE ... INNER JOIN

Example:

UPDATE sm
SET sm.SysConfId = sc.Id
FROM tblSKUMat sm
INNER JOIN tblSysConf sc
ON sm.Id = sc.Id