Huge Tables in the Database

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

create proc Show_HugeTables

(

@top varchar(3)

)

as begin

/*************************************************************************************************

Purpose: To list the size of all tables in the database in descending order (that is biggere tables first).

Output can be restricted to top n tables. That is you can pass the value 3 to @top parameter to

see the top 3 biggest tables in your database.

NOTE: Always create this procedure in the same database in which you want to check the table size.

Written by: Afroz Azam

Tested on: SQL Server 2000,2005

Date created: January-07-2010

Date modified:

Email: syedazamdxb@gmail.com

Examples:

To list all the user tables in the database along with their sizes:

EXEC Show_HugeTables

To see the top three biggest tables in your database:

EXEC Show_HugeTables '3'

*************************************************************************************************/

declare @table as nvarchar(500) ,

@str as nvarchar(500),

@str1 as nvarchar(1500)

select name, 'N' as stat into #temp from sysobjects where xtype = 'U'

create table #temp1

( name varchar(50),

rows bigint,

reserved varchar(100),

data varchar(100),

inex_size varchar(100),

unused varchar(100)

)

declare cur1 cursor local for

select name from #temp

open cur1

fetch next from cur1 into @table

while @@fetch_status =0

begin

set @str = 'sp_spaceused' + ' '+ @table

insert into #temp1

exec sp_executesql @str

fetch next from cur1 into @table

end

set @str1 ='select top '+ @top +' name,left(data,len(data)-2) as DATA_KB,left(data,len(data)-2)/1024 as DATA_MB ,'

+'left(data,len(data)-2)/1024/1024 as DATA_GB from #temp1 order by left(data,len(data)-2)/1024 desc'

exec sp_executesql @str1

end

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

SQL Server Database.

How to Shrink Database with the help of procedure.

--exec DATABASE..DBNAME_ShrinkDB

CREATE PROCEDURE DBNAME_ShrinkDB

AS

DECLARE @DBName VarcHaR(228)

DECLARE @DB VarcHaR(128)

DECLARE @LG VarcHaR(128)

DECLARE @DBShrink varchar(8000)

DECLARE @LGShrink varchar(8000)

DECLARE @Backuplog varchar(8000)

SET @DBName = '[DBNAME]'

SET @DBName = SubString(@DBName,2,len(@DBName) -2)

SET @DB = (Select Name From Master..SysAltFiles Where DBID = (Select dbid from master..sysdatabases where name = @DBName) and GroupID = 1)

SET @LG = (Select Name From Master..SysAltFiles Where DBID = (Select dbid from master..sysdatabases where name = @DBName) and GroupID = 0)

select @DB,@LG

--Check DB Space Usage

SET @DBShrink = 'DBCC UPDATEUSAGE(' + @DBName + ')'

EXEC (@DBShrink)

--Check Allocation

SET @DBShrink = 'DBCC CHECKALLOC(' + @DBName + ')'

EXEC (@DBShrink)

--Check DB

SET @DBShrink = 'DBCC CHECKDB(' + @DBName + ')'

EXEC (@DBShrink)

--Check Catalogue

SET @DBShrink = 'DBCC CHECKCATALOG(' + @DBName + ')'

EXEC (@DBShrink)

--Shrink DB

SET @DBShrink = 'DBCC SHRINKDATABASE(' + @DBName + ')'

PRINT (@DBShrink)

EXEC (@DBShrink)

--Shrink DB File

SET @DBShrink = 'DBCC SHRINKFILE (' + @DB + ')'

PRINT (@DBShrink)

EXEC (@DBShrink)

--Shrink LG File

SET @Backuplog = 'Backup log ' + @DBName + ' WITH TRUNCATE_ONLY'

PRINT (@Backuplog)

EXEC (@Backuplog)

SET @DBShrink = 'DBCC SHRINKFILE (' + @LG + ')'

PRINT (@DBShrink)

EXEC (@DBShrink)

Dynamic Query.
The below is the sample script for the dynamic query
CREATE PROC BI_PoCheck
(
@po VARCHAR(10)
)
AS
DECLARE @wh AS INT
DECLARE @ser AS NVARCHAR(100)
DECLARE @ser1 AS NVARCHAR(100)
DECLARE @str AS NVARCHAR(600)
SET @wh =
(SELECT branchid FROM MY_branch
WHERE rowguid IN (
SELECT branchid FROM MY_purchorddetail
WHERE purchaseorderid IN
(SELECT rowguid FROM MY_purchaseorder
WHERE purchaseorderid = @po)))
IF @wh = 3200
BEGIN
SET @ser = '[10.0.0.XX].Database1'
SET @ser1 = '[10.0.0.XX]'
END
ELSE IF @wh = 4100
BEGIN
SET @ser = '[10.0.0.XX].Database2'
SET @ser1 = '[10.0.0.XX]'
END
ELSE IF @wh = 2200
BEGIN
SET @ser = '[10.0.0.XX].Database3'
SET @ser1 = '[10.0.0.XX]'
END
ELSE IF @wh = 6200
BEGIN
SET @ser = '[10.0.0.XX].Database4'
SET @ser1 = '[10.0.0.XX]'
END
ELSE IF @wh = 2000
BEGIN
SET @ser = '[10.0.0.YY].Database_3'
SET @ser1 = '[10.0.0.YY]'
end
ELSE IF @wh = 1000
BEGIN
SET @ser = '[10.0.0.YY].Database_5'
SET @ser1 = '[10.0.0.YY]'
END
ELSE IF @wh = 3000
BEGIN
SET @ser = '[10.0.0.YY].Database_1'
SET @ser1 = '[10.0.0.YY]'
end
ELSE IF @wh = 4000
BEGIN
SET @ser = '[10.0.0.YY].Database_2'
SET @ser1 = '[10.0.0.YY]'
END
ELSE IF @wh = 6000
BEGIN
SET @ser = '[10.0.0.YY].Database_4'
SET @ser1 = '[10.0.0.YY]'
END
-- po checking at WH
CREATE TABLE #podatachk
(
poid INT
)
SET @str = 'insert into #podatachk (poid)'+ 'SELECT purchaseorderid FROM ' + @ser + '.dbo.MY_purchaseorder WHERE purchaseorderid ='''+@po+''''
PRINT @str
EXEC sp_executesql @str
DECLARE @cnt AS INT
SELECT @cnt = COUNT(1) FROM #podatachk
IF @cnt = 0
BEGIN
SELECT problem = 'PO is NOT present'
RETURN
END
ELSE IF @cnt <> 0
BEGIN
SELECT comment = 'Po is present'
END
-------checking detail at WH ---------------------------
CREATE TABLE #podetchk
(
poname INT
)
SET @str = 'insert into #podetchk (poname) '+ 'SELECT p.name FROM ' + @ser +'.dbo.MY_purchorddetail pd INner joIN ' +
'MY_purchaseorder p on p.rowguid = pd.purchaseorderid WHERE p.purchaseorderid =''' + @po +''''
EXEC sp_executesql @str
DECLARE @cntpd AS INT
SELECT @cntpd = COUNT(poname) FROM #podetchk
IF @cntpd = 0
BEGIN
SELECT problem = 'Detail is NOT present'
GOTO detailNOTexist
END
ELSE IF @cntpd <> 0
BEGIN
SELECT comment = 'Detail is ok '
END
SELECT itemmASterid,name into #itemuae FROM MY_itemmASter
WHERE rowguid IN
(
SELECT itemmASterid FROM
MY_purchorddetail pod INner joIN MY_purchaseorder po
on pod.purchaseorderid = po.rowguid WHERE po.purchaseorderid = @po
and pod.isdeleted = 0
)
CREATE TABLE #itemuaewh
(
id VARCHAR(15),
pname VARCHAR(100)
)
SET @str = 'insert into #itemuaewh (id,pname) '+ 'SELECT itemmASterid,name FROM ' + @ser + '.dbo.MY_itemmASter WHERE rowguid IN ' +
+ '( SELECT itemmASterid FROM ' + @ser + '.dbo.MY_purchorddetail pod INner joIN ' +
+ @ser + '.dbo.MY_purchaseorder po on ' + 'pod.purchaseorderid = po.rowguid WHERE po.purchaseorderid =''' + @po +'''' +
' and pod.isdeleted = 0 )'
PRINT @str
EXEC sp_executesql @str
DECLARE @uae AS INT
DECLARE @uaewh AS INT
SELECT @uae = COUNT(1) FROM #itemuae
SELECT @uaewh = COUNT(1) FROM #itemuaewh
IF @uae <> @uaewh
BEGIN
SELECT itemmASterid,name FROM #itemuae WHERE itemmASterid collate Database_default
NOT IN (SELECT id FROM #itemuaewh)
SELECT Solution = 'Make Dirty of this Item'
GOTO itemNOTpresent
END
ELSE IF @uae = @uaewh
BEGIN
SELECT Comments = 'Items are ok '
END
------------------end -----------------------------------
-----------Vendor checking at HO-------------------------
SELECT Name,vendorid into #vendor FROM MY_vendor WHERE
rowguid IN ( SELECT vendorid FROM MY_purchaseorder WHERE purchaseorderid = @po)
-- at WH
CREATE TABLE #vendorwh
(
vname VARCHAR(50)
)
SET @str ='insert into #vendorwh (vname) '+
+ 'SELECT Name FROM ' + @ser + '.dbo.MY_vendor WHERE rowguid IN ( SELECT vendorid FROM ' +
+ @ser + '.dbo.MY_purchaseorder WHERE purchaseorderid = ''' + @po +''')'
PRINT @str
EXEC sp_executesql @str
DECLARE @ven INT
SELECT @ven = COUNT(1) FROM #vendorwh
IF @ven = 0
BEGIN
SELECT Problem = 'Vendor is NOT present'
SELECT Solution = 'Make Dirty this Vendor'
SELECT * FROM #vendor
END
ELSE IF @ven <> 0
BEGIN
SELECT Comments = 'No issue with Vendor'
END
------------------------------------- vendor close ----------------------------------
SELECT p.purchaseorderid,
p.polINecntr,
COUNT(pd.name) AS 'PoDetailCnt',
p.poitemtotal ,
sum(pd.qtyord) AS 'PODetQty',
p.podate,
p.poAmount,
b.name,
p.status
into #pochkuae
FROM MY_purchaseorder p INner joIN MY_purchorddetail pd on p.rowguid = pd.purchaseorderid
INner joIN MY_branch b on b.rowguid = pd.branchid
WHERE p.purchaseorderid = @po
and pd.isdeleted = 0
group by
p.purchaseorderid,
p.podate,
p.poAmount,
b.name,
p.polINecntr,
p.poitemtotal,
p.status
CREATE TABLE #pochkwh
(
purchaseorderid INT,
polINecntr INT,
podetailcnt INT,
poitemtotal INT,
podetqty INT,
podate DATETIME,
poAmount INT,
bname VARCHAR(50),
status char(4)
)
SET @str = 'insert into #pochkwh (purchaseorderid,polINecntr,podetailcnt,poitemtotal,podetqty,podate,poAmount,bname,status)' +
'SELECT p.purchaseorderid,p.polINecntr, COUNT(pd.name) ,p.poitemtotal ,sum(pd.qtyord),' +
+ 'p.podate,p.poAmount,b.name,p.status FROM ' + @ser + '.dbo.MY_purchaseorder p INner joIN ' +
+ @ser + '.dbo.MY_purchorddetail pd on p.rowguid = pd.purchaseorderid ' +
+ 'INner joIN' + @ser1 + '.DatabaseCommon.dbo.MY_branch b on b.rowguid = pd.branchid ' +
+ 'WHERE p.purchaseorderid = '''+ @po + ''' and pd.isdeleted = 0 group by p.purchaseorderid,p.podate,p.poAmount,b.name,
p.polINecntr,p.poitemtotal,p.status'
PRINT @str
EXEC sp_executesql @str
SELECT * FROM #pochkuae
SELECT * FROM #pochkwh
GOTO fINish
itemNOTpresent:
SELECT Comments = 'comments 1'
RETURN
poNOTexist:
SELECT Comments = 'comments 2'
RETURN
detailNOTexist:
SELECT Comments = 'comments 3'
RETURN
fINish: SELECT 'comments 4 '

Oracle Retail ERP( Enterprise Resource Planning)

Now a days ERP is essential for the rapid growth and success of a company.

Learn yoga and practice.

Yoga is becoming famous in todays world, a natural way to maintain a healthy mind and a healthy body.
Recently Yoga work shop was conducted in INDIA CLUB Dubai ,by Rumana Rab , a Yoga Expert.
http://www.indiaclubdubai.com/html/Events.asp?EID=607&cat=1

Contact Rumana at

rumanarab@yahoo.com

What is 'Limiting Beliefs??

What is 'Limiting Beliefs??


Are you following every Law of Attraction principle and sometimes struggle to manifest? Most people do at some point on their LOA journey. This is normally because we have limiting beliefs that hold us back from manifesting our hearts desore. Many people don't realise they have capped their true potential through experiences they had as a child. You might think its crazy but it's true. We have all had little voices in our heads telling us 'youre not good enough', 'you cant be a success', 'you don't deserve it', 'there's not enough to go round'. These are what we call 'Limiting Beliefs', they're decisions we've made about ourself at some point in our childhood and we're re-living these decisions every day in our adult life. These beliefs literally hold us back from achieveing our true potential.



If you want to check out if you have any limiting beliefs and you want to have a go at clearing them for good, then join world renowned Lynda Dyer on her FREE webinar this Thursday at 8pm. Lynda was filmed for the movie The Secret, she was Bob Procters right hand girl and she was recently a Master at the Mastes Gathering in San Diego.

Don’t Become Complacent
If you are getting outstanding results, riding the crest of a wave of success is no guarantee of continued success. Many people have made such an error and it has cost them dearly. They became complacent.
I can clearly remember when APECO, American Photo Copy Equipment Company dominated the photocopy market. APECO was a giant; they were successfully selling their photocopiers in 152 countries. In fact, they were the first to bring a photocopier into the office environment. A piece of equipment which, today, is almost a necessity in every office.
Meanwhile a gentleman from Rochester, New York was looking for a way to make a piece of equipment that would turn out a better copy. Since that is what Chester Carelson was looking for, that is exactly what he found. Today, everyone around the world recognizes Xerox and hardly anyone remembers APECO, because they have vanished.
The marketplace is very fickle. When you present a better or more efficient product, the people go with it. That’s progress.
The poet Braely penned this:
For the best verse hasn’t been rhymed yet.
The best house hasn’t been planned.
The highest peak hasn’t been climbed yet.
The mightiest rivers aren’t spanned.
Don’t worry and fret, faint hearted.
The chances have just begun.
For the best jobs haven’t been started.
The best work hasn’t been done.
Invest a few minutes today and every day thinking of ways to do whatever you are doing better. You could be doing your job well, you might even be the best, but I can assure you there is always a Chester Carelson lurking in the wings. Keep doing what you did to win. Keep improving what you are doing.

Don’t Think In Reverse

You will never obtain any measure of material wealth if you insist upon living your life as if you were looking back through the rear view mirror of an automobile. Nevertheless, this seems to be a common error, which many people have turned into a habit. Remember the old adage which says, let the dead bury the dead. Stop looking back in your life and worrying about things which have already occurred and which you can no longer alter. Pursuing that kind of mental activity will never lead to anything worthwhile in your life.
You should understand moreover, that all of the great achievers of the past have been visionary figures. They were men and women who projected into the future and did not belabor over the past. They thought of what could be, rather than what already was, and then they moved themselves into action, bringing these things into fruition.

Leland Val Vandewall offers some excellent advice on this subject. Let us not look back in anger, nor forward in fear, but around us in awareness.
If you have been guilty of allowing your sales sheets, your bank account or the x-rays the doctor takes of your body, to control the way you view your sales, financial position, or health, you will never see any marked improvement in your life in any of these areas. However, if you let the present, physical results serve only as an indication of the images, which you have been holding in the past and then proceed to look into the bright future and to build an image of the good that you desire; you will see the image materialize.
Look up, look ahead and form the image of the life you choose to live.