Link Tables

Run this on the Database to Link the Tables

-- Step 2: Declare a table variable to store table names

DECLARE @Tables TABLE (TableName NVARCHAR(128));

-- Step 3: Insert the list of tables, including 'Stores'

INSERT INTO @Tables (TableName) VALUES

('PS_BrandDiscounts'),

('PS_CategoryDiscounts'),

('PS_ClientTypes'),

('PS_DateFrames'),

('PS_ItemDiscounts'),

('PS_ItemTypeDiscounts'),

('PS_MenuGroupItem'),

('PS_OrderTypes'),

('PS_PriceSchedule'),

('PS_RequiredBrands'),

('PS_RequiredCategories'),

('PS_RequiredItems'),

('PS_RequiredItemTypes'),

('PS_TimeFrames'),

('PS_WeekDays'),

('tblProducts'),

('PG_PromptGroups'),

('PG_PromptMenuItems'),

('PG_PromptSubGroups'),

('PG_Selected_PromptSubGroups'),

('tblProductPricing'),

('tblProductPriceCategory'),

('tblUnitType'),

('tblUnitConversion'),

('tblUnitAmountType'),

('tblTax'),

('tblShortInventory'),

('PB_Main'),

('PB_Main_Associations'),

('Menu_ClientTypes'),

('Menu_DateFrames'),

('Menu_MenuItems'),

('Menu_OrderTypes'),

('Menu_TimeFrames'),

('Menu_WeekDays'),

('ItemType'),

('ItemType_Categories'),

('ItemType_DisplayAssignment'),

('DisplayMenu'),

('Stores'); -- Added Stores to the list

-- Step 4: Declare variables for dynamic SQL

DECLARE @CreateSynonymSQL NVARCHAR(MAX) = '';

DECLARE @TableName NVARCHAR(128);

-- Step 5: Loop through the list and generate CREATE SYNONYM statements

DECLARE table\_cursor CURSOR FOR

SELECT TableName FROM @Tables;

OPEN table_cursor;

FETCH NEXT FROM table_cursor INTO @TableName;

WHILE @@FETCH_STATUS = 0

BEGIN

SET @CreateSynonymSQL +=

'IF OBJECT_ID(''dbo.' + @TableName + ''', ''SN'') IS NOT NULL ' +

'DROP SYNONYM dbo.' + @TableName + '; ' +

'CREATE SYNONYM dbo.' + @TableName + ' FOR grocery.dbo.' + @TableName + '; ';

FETCH NEXT FROM table_cursor INTO @TableName;

END

CLOSE table_cursor;

DEALLOCATE table_cursor;

-- Step 6: Execute the dynamic SQL to create the synonyms

EXEC sp_executesql @CreateSynonymSQL;