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 '