MSSQL QUERIES

OVERVIEW

USE THE OUTLINE ON THE RIGHT TO NAVIGATE TO THE DESIRED QUERY

This page contains various MSSQL queries that will amend, alter, or remove essential parts of an existing MSSQL Server.

PROCEED WITH CAUTION ! PROCEED WITH CAUTION ! PROCEED WITH CAUTION

---

QUERY TO RESET DB WHILE KEEPING INVENTORY, VENDORS, CUSTOMERS

-- Truncate the specified tables
TRUNCATE TABLE [Adjustments];
TRUNCATE TABLE [AlternateSKU];
TRUNCATE TABLE [Appointments];
TRUNCATE TABLE [Audit];
TRUNCATE TABLE [AuthPurchaser];
TRUNCATE TABLE [Bank];
TRUNCATE TABLE [Bank_Checks];
TRUNCATE TABLE [Bank_Checks_Bank];
TRUNCATE TABLE [Bank_Recon];
TRUNCATE TABLE [BatchSettle];
TRUNCATE TABLE [BeverageCode];
TRUNCATE TABLE [BillingInvoices];
TRUNCATE TABLE [BillingPayments];
TRUNCATE TABLE [BillingReminder];
TRUNCATE TABLE [biochecks_CheckMaintanance];
TRUNCATE TABLE [biochecks_CustData];
TRUNCATE TABLE [biochecks_DetailsAmounts];
TRUNCATE TABLE [BioMetric];
TRUNCATE TABLE [BuyDown];
TRUNCATE TABLE [CanceledInvoices];
TRUNCATE TABLE [CanceledItems];
TRUNCATE TABLE [CaseSku];
TRUNCATE TABLE [CashierMonitoring];
TRUNCATE TABLE [Category];
TRUNCATE TABLE [CDTFA_F];
TRUNCATE TABLE [CheckFile];
TRUNCATE TABLE [Checkout];
TRUNCATE TABLE [Checks];
TRUNCATE TABLE [ChecksDetails];
TRUNCATE TABLE [ClockIn];
TRUNCATE TABLE [CloseDaydate];
TRUNCATE TABLE [CreditCard];
TRUNCATE TABLE [CreditConfig];
TRUNCATE TABLE [Customer_Ledger];
TRUNCATE TABLE [DailyDeal];
TRUNCATE TABLE [DailyNumber];
TRUNCATE TABLE [DandHOrder];
TRUNCATE TABLE [Delivery];
TRUNCATE TABLE [Discontinued_items];
TRUNCATE TABLE [DiscountDescription];
TRUNCATE TABLE [DiscountDetail];
TRUNCATE TABLE [DriveThru];
TRUNCATE TABLE [FeaturedItems];
TRUNCATE TABLE [FieldPro_Details];
TRUNCATE TABLE [FieldPro_Main];
TRUNCATE TABLE [FinancialOperation];
TRUNCATE TABLE [GiftCard];
TRUNCATE TABLE [GiftCardTrans];
TRUNCATE TABLE [HandHeld_NewItems];
TRUNCATE TABLE [HandHeld_NewPrices];
TRUNCATE TABLE [Invoices];
TRUNCATE TABLE [InvoiceDetails];
TRUNCATE TABLE [ItemMessages];
TRUNCATE TABLE [KDS];
TRUNCATE TABLE [Kit];
TRUNCATE TABLE [Labels];
TRUNCATE TABLE [Labels_Food_Batch];
TRUNCATE TABLE [Labels_Food_Cat];
TRUNCATE TABLE [Layaway];
TRUNCATE TABLE [LayawayDetails];
TRUNCATE TABLE [LayawayPayments];
TRUNCATE TABLE [LayawaySetup];
TRUNCATE TABLE [LiveCashierData];
TRUNCATE TABLE [Locks];
TRUNCATE TABLE [LotMatrix];
TRUNCATE TABLE [MercuryBatchSum];
TRUNCATE TABLE [Messages];
TRUNCATE TABLE [MixandMatch];
TRUNCATE TABLE [NoSale];
TRUNCATE TABLE [OfflineTables];
TRUNCATE TABLE [OldPasswords];
TRUNCATE TABLE [OnlineOrderDetails];
TRUNCATE TABLE [OnlineOrderMain];
TRUNCATE TABLE [Open_Shift];
TRUNCATE TABLE [OrderList];
TRUNCATE TABLE [PONumbers];
TRUNCATE TABLE [PriceChangeHistory];
TRUNCATE TABLE [PriceGroup];
TRUNCATE TABLE [Printed];
TRUNCATE TABLE [Pull];
TRUNCATE TABLE [PurchaseOrder];
TRUNCATE TABLE [PurchaseOrderDetails];
TRUNCATE TABLE [Receiving];
TRUNCATE TABLE [Receiving_Items];
TRUNCATE TABLE [RemovedItems];
TRUNCATE TABLE [ReportDates];
TRUNCATE TABLE [ReturnCode];
TRUNCATE TABLE [ROA];
TRUNCATE TABLE [SalesReps];
TRUNCATE TABLE [Schedule];
TRUNCATE TABLE [SecurityOverideLog];
TRUNCATE TABLE [Serial];
TRUNCATE TABLE [ServiceDept];
TRUNCATE TABLE [ServiceDept1];
TRUNCATE TABLE [ServiceDeptCheckList];
TRUNCATE TABLE [ServiceDeptDiagCodes];
TRUNCATE TABLE [ServiceDeptDiagnostic];
TRUNCATE TABLE [ShipPay];
TRUNCATE TABLE [ShippingLabel];
TRUNCATE TABLE [SlideShow];
TRUNCATE TABLE [SubMenu2];
TRUNCATE TABLE [TableLayout];
TRUNCATE TABLE [TableLayoutSections];
TRUNCATE TABLE [TerminalMessage];
TRUNCATE TABLE [TicketSystem];
TRUNCATE TABLE [TicketSystemDetail];
TRUNCATE TABLE [TicketSystemStatus];
TRUNCATE TABLE [TimeClock];
TRUNCATE TABLE [Vendor_Backoffice];
TRUNCATE TABLE [Vendors];
TRUNCATE TABLE [WebNewCustomerForm];
TRUNCATE TABLE [Weborder_Details];
TRUNCATE TABLE [Weborder_Main];
TRUNCATE TABLE [WorkOrder];
TRUNCATE TABLE [WorkOrder_Mobile];
TRUNCATE TABLE [WorkOrderDetails];
TRUNCATE TABLE [WorkOrderDetails_Mobile];
TRUNCATE TABLE [YTD_Sales];

-- Set [OnHand] column to 0 in the [Inventory] table
UPDATE [Inventory]
SET [OnHand] = '0';

-- Reset [Keyboard] Values
UPDATE [Keyboard]
SET [Visible] = 'True';
UPDATE [Keyboard]
SET [Text] = NULL;
UPDATE [Keyboard]
SET [Bold] = 'True';
UPDATE [Keyboard]
SET [Italic] = 'False';
UPDATE [Keyboard]
SET [Strikeout] = 'False';
UPDATE [Keyboard]
SET [Underline] = 'False';
UPDATE [Keyboard]
SET [Size] = '8.25';

-- Reset [NCR_KeyMap] Values
UPDATE [NCR_KeyMap]
SET [is_dept] = 'False';
UPDATE [NCR_KeyMap]
SET [is_tender] = 'False';
UPDATE [NCR_KeyMap]
SET [is_amount] = 'False';
UPDATE [NCR_KeyMap]
SET [txtupc] = NULL;
UPDATE [NCR_KeyMap]
SET [tender] = NULL;
UPDATE [NCR_KeyMap]
SET [amount] = '0';

MSA CHECKPOINTS IN SQL

--checks the sku
select UPCCode,ItemDescription,sku from Inventory where msa=1 and ( sku is null or sku='')

--checks to see if promotion indicator is missing
select UPCCode,ItemDescription,sku, promotion from Inventory where msa=1 and (Promotion is null or Promotion='')

--missing promotion description if it has promotion checked
select UPCCode,ItemDescription,sku, promotion, promdescrip from Inventory where msa=1 and Promotion='Y' and (promdescrip is null or promdescrip='')

--item has promotion description but the promotion indicator is set to N
select UPCCode,ItemDescription, promdescrip   from Inventory where msa=1  and  promotion='N'and  promdescrip<>''

--checks item desciption for promotion text if amount is included in item desctiption then its should be promo=Y
select UPCCode,ItemDescription, promdescrip, promotion   from Inventory where msa=1  and  promotion='N'and  ItemDescription LIKE '%$%'

--checks OnHand to make sure it isnt an unreasonable number
SELECT UPCCode, ItemDescription, DepartmentID, Price, Units, Promotion, MPC, MeasureCode, SKU, MSACatCode, MSA, PromDescrip, OnHand, DistributerShip,MSA_FloorReturns FROM Inventory  WHERE MSA=1 and OnHand>5000

--Missing MPC for any item that has promotion checked
select UPCCode,ItemDescription,sku, promotion, promdescrip, MPC from Inventory where msa=1 and Promotion='Y' and (MPC is null or MPC='')

--Checks to see if the MSa category code has the right amount of digits its total of 6
select UPCCode,ItemDescription,sku, MSACatCode from Inventory where msa=1  and (MSACatCode is null or LEN(MSACatCode) <6)


--checks the units makes sure its not blank or 0
select UPCCode,ItemDescription, units from Inventory where msa=1  and (units is null or units='' or units=0)

--checks onhand to make sure that we dont have negative numbers, blank inventory or decimal qty
select UPCCode,ItemDescription, onhand from Inventory where msa=1  and (onhand is null or onhand<0 or onhand!= ROUND (onhand,  0))


--Customers Section

--checks to see if the accepts promo field is available
select CustomerID,CompanyName,StreetShip,CityShip,StateShip,ZipCodeShip,ClassofTrade,AcceptPromo from Customers where msa=1 and (AcceptPromo is null or AcceptPromo='')

--checks the class of trade if its missing
select CustomerID,CompanyName,StreetShip,CityShip,StateShip,ZipCodeShip,ClassofTrade,AcceptPromo from Customers where msa=1 and (ClassofTrade is null or ClassofTrade='')

--this checks the Company Name, Street Address, City , State , Zip
select CustomerID,CompanyName,StreetShip,CityShip,StateShip,ZipCodeShip,ClassofTrade,AcceptPromo, MSA from Customers where msa=1 and (
(CompanyName is null or CompanyName='') or (StreetShip is null or StreetShip='') or (CityShip is null or CityShip='') 
 or (StateShip is null or StateShip='')  or (ZipCodeShip is null or ZipCodeShip='') )

--check the state jusrisdiction its the state ship 
select CustomerID,CompanyName,StreetShip,CityShip,StateShip,ZipCodeShip,ClassofTrade,AcceptPromo from Customers where msa=1 and LEN(StateShip) <>2




SELECT         Receiving.IDReceived, Receiving.po, Vendors.VendorID,Vendors.Name, Vendors.Street, Vendors.City, Vendors.State, Vendors.ZipCode, Vendors.Country, Vendors.Number
 FROM            Inventory INNER JOIN
						                          Receiving_Items ON Inventory.UPCCode = Receiving_Items.PLU INNER JOIN
						                         Receiving ON Receiving_Items.IDReceived = Receiving.IDReceived INNER JOIN
					                          Vendors ON Receiving.VendorID = Vendors.VendorID
WHERE        (Inventory.FLT = 1) AND (Inventory.non_stamp = 0) AND (Inventory.Discontinued = 0)  and  ((CAST(Receiving.Date AS DATE)>='2022-03-04' and CAST(Receiving.Date AS DATE)<='2022-03-05'))
 and ((Vendors.Name is  null or Vendors.Name='') or (Vendors.Street is  null or Vendors.Street ='') or (Vendors.Street is  null or Vendors.Street ='') or (Vendors.State is  null or Vendors.State='') or (Vendors.ZipCode is  null or Vendors.ZipCode='')
 or (Vendors.Country is  null or Vendors.Country='') or (Vendors.Number is  null or Vendors.Number='' ) or (LEN(Vendors.State) <> 2) or  (LEN(Vendors.country) <> 2) or (LEN(Vendors.ZipCode) <> 5) )
group by     Receiving.IDReceived, Receiving.po, Vendors.VendorID, Receiving.PO, Vendors.Name, Vendors.Street, Vendors.City, Vendors.State, Vendors.ZipCode, Vendors.Country, Vendors.Number

ADD MSA TO EXISTING SITE

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Inventory ADD
	FLT bit NULL,
	Sticks float(53) NULL,
	msa_status nchar(10) NULL,
	UOM nchar(10) NULL,
	Manuf_code nchar(10) NULL,
	non_stamp bit NULL
GO
ALTER TABLE dbo.Inventory SET (LOCK_ESCALATION = TABLE)
GO
COMMIT

update inventory set flt=0, non_stamp=0, sticks=0




/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.SalesReps ADD
	UserID nvarchar(MAX) NULL,
	Password nvarchar(MAX) NULL,
	All_Web_Users bit NULL,
	Web_Accounts nvarchar(MAX) NULL
GO
ALTER TABLE dbo.SalesReps SET (LOCK_ESCALATION = TABLE)
GO
COMMIT



USE [POS]
GO

/****** Object:  Table [dbo].[CDTFA_F]    Script Date: 10/2/2020 10:45:23 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[CDTFA_F](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[OrderDate] [date] NULL,
	[qty] [float] NULL,
	[upc] [nvarchar](max) NULL,
	[product_name] [nvarchar](max) NULL,
	[sticks] [float] NULL,
	[uom] [nchar](3) NULL,
	[msa_stat] [nvarchar](max) NULL,
	[manf_code] [nvarchar](max) NULL,
	[buyer_id] [int] NULL,
	[buyer_name] [nvarchar](max) NULL,
	[buyer_street] [nvarchar](max) NULL,
	[buyer_city] [nvarchar](max) NULL,
	[buyer_state] [nvarchar](max) NULL,
	[buyer_zip] [nvarchar](max) NULL,
	[buyer_country] [nvarchar](max) NULL,
	[buyer_ein] [nvarchar](max) NULL,
	[buyer_rec_id] [int] NULL,
 CONSTRAINT [PK_CDFA810] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



CREATE NONCLUSTERED INDEX IDX_inventory_Flt_Non_Stamp
ON [dbo].[Inventory] ([FLT],[non_stamp])
INCLUDE ([ItemDescription],[Sticks],[msa_status],[UOM],[Manuf_code])

QUERY TO CLEAR OUT ENTIRE DB WHILE KEEPING DESIGN

-- Truncate and reseed identity columns
TRUNCATE TABLE [Adjustments];
TRUNCATE TABLE [AlternateSKU];
TRUNCATE TABLE [Appointments];
TRUNCATE TABLE [Audit];
TRUNCATE TABLE [AuthPurchaser];
TRUNCATE TABLE [Bank];
TRUNCATE TABLE [Bank_Checks];
TRUNCATE TABLE [Bank_Checks_Bank];
TRUNCATE TABLE [Bank_Recon];
TRUNCATE TABLE [BatchSettle];
TRUNCATE TABLE [BeverageCode];
TRUNCATE TABLE [BillingInvoices];
TRUNCATE TABLE [BillingPayments];
TRUNCATE TABLE [BillingReminder];
TRUNCATE TABLE [BioMetric];
TRUNCATE TABLE [BuyDown];
TRUNCATE TABLE [CanceledInvoices];
TRUNCATE TABLE [CanceledItems];
TRUNCATE TABLE [CaseSku];
TRUNCATE TABLE [CashierMonitoring];
TRUNCATE TABLE [Category];
TRUNCATE TABLE [CDTFA_F];
TRUNCATE TABLE [CheckFile];
TRUNCATE TABLE [Checkout];
TRUNCATE TABLE [Checks];
TRUNCATE TABLE [ChecksDetails];
TRUNCATE TABLE [ClockIn];
TRUNCATE TABLE [CreditCard];
TRUNCATE TABLE [Customer_Ledger];
TRUNCATE TABLE [Customers];
TRUNCATE TABLE [DailyDeal];
TRUNCATE TABLE [DandHItemList];
TRUNCATE TABLE [DandHOrder];
TRUNCATE TABLE [Department];
TRUNCATE TABLE [FinancialOperation];
TRUNCATE TABLE [GiftCard];
TRUNCATE TABLE [GiftCardTrans];
TRUNCATE TABLE [HandHeld_NewItems];
TRUNCATE TABLE [HandHeld_NewPrices];
TRUNCATE TABLE [Inventory];
TRUNCATE TABLE [InvoiceDetails];
TRUNCATE TABLE [Invoices];
TRUNCATE TABLE [ItemMessages];
TRUNCATE TABLE [KDS];
TRUNCATE TABLE [Kit];
TRUNCATE TABLE [Labels];
TRUNCATE TABLE [Layaway];
TRUNCATE TABLE [LayawayDetails];
TRUNCATE TABLE [LayawayPayments];
TRUNCATE TABLE [MercuryBatch];
TRUNCATE TABLE [MercuryBatchSum];
TRUNCATE TABLE [MixandMatch];
TRUNCATE TABLE [NoSale];
TRUNCATE TABLE [OfflineTables];
TRUNCATE TABLE [OldPasswords];
TRUNCATE TABLE [OrderList];
TRUNCATE TABLE [PONumbers];
TRUNCATE TABLE [PriceChangeHistory];
TRUNCATE TABLE [PriceGroup];
TRUNCATE TABLE [Printed];
TRUNCATE TABLE [Pull];
TRUNCATE TABLE [PurchaseOrder];
TRUNCATE TABLE [Receiving];
TRUNCATE TABLE [Receiving_Items];
TRUNCATE TABLE [ReturnCode];
TRUNCATE TABLE [SalesReps];
TRUNCATE TABLE [Serial];
TRUNCATE TABLE [ServiceDept];
TRUNCATE TABLE [ServiceDeptCheckList];
TRUNCATE TABLE [ServiceDeptDiagCodes];
TRUNCATE TABLE [ShipPay];
TRUNCATE TABLE [ShippingLabel];
TRUNCATE TABLE [SlideShow];
TRUNCATE TABLE [TerminalMessage];
TRUNCATE TABLE [TicketSystem];
TRUNCATE TABLE [TicketSystemDetail];
TRUNCATE TABLE [TicketSystemStatus];
TRUNCATE TABLE [TimeClock];
TRUNCATE TABLE [Users];
TRUNCATE TABLE [Vendors];
TRUNCATE TABLE [WICFTP];
TRUNCATE TABLE [WICPriceGroup];
TRUNCATE TABLE [WicSerials];
TRUNCATE TABLE [WorkOrder];
TRUNCATE TABLE [WorkOrder_Mobile];
TRUNCATE TABLE [WorkOrderDetails];
TRUNCATE TABLE [WorkOrderDetails_Mobile];
TRUNCATE TABLE [YTD_Sales];

-- These tables should only be updated
UPDATE ColumnsRows1
SET PLU = NULL, Description = NULL, Graphic = NULL, Image = NULL;
UPDATE Pages
SET PLU = NULL, Description = NULL, Graphic = NULL;
UPDATE Keyboard
SET Text = NULL, ItemUPC = NULL;
UPDATE SubMenuButtons
SET PLU = NULL, Description = NULL, BackgroundColor = NULL;
UPDATE TableLayout
SET PLU = NULL, Description = NULL, BackgroundColor = NULL, Graphic = NULL, Image = NULL;

SLOW DB STATS

-- First run the query below to generate the proper query
-- Then select the query that was generated and paste it into a new query and run

SELECT DISTINCT 'DROP STATISTICS '
+ QUOTENAME(SCHEMA_NAME(ob.Schema_id)) + '.'
+ QUOTENAME(OBJECT_NAME(s.object_id)) + '.' +
QUOTENAME(s.name) DropStatisticsStatement
FROM sys.stats s
INNER JOIN sys.Objects ob ON ob.Object_id = s.object_id
WHERE SCHEMA_NAME(ob.Schema_id) <> 'sys'
AND Auto_Created = 1