Dynamically Create Synonyms

dynamically_create_synonyms.sql

-- Create a table variable to store the table names and synonyms
DECLARE @TableSynonyms TABLE (
    TableName NVARCHAR(128),
    TableSchema NVARCHAR(128),
    SynonymName NVARCHAR(128),
    SynonymSchema NVARCHAR(128)
);

-- Insert the table names and synonyms into the table variable
-- You can change the logic for generating the synonym names and schemas as per your requirement
INSERT INTO @TableSynonyms (TableName, TableSchema, SynonymName, SynonymSchema)
SELECT name, schema_name(schema_id), name, 'dbo'
FROM sys.tables;

-- Declare a cursor to loop through the table variable
DECLARE @TableName NVARCHAR(128), @TableSchema NVARCHAR(128), @SynonymName NVARCHAR(128), @SynonymSchema NVARCHAR(128);
DECLARE TableSynonymCursor CURSOR FOR
SELECT TableName, TableSchema, SynonymName, SynonymSchema FROM @TableSynonyms;

-- Open the cursor and fetch the first row
OPEN TableSynonymCursor;
FETCH NEXT FROM TableSynonymCursor INTO @TableName, @TableSchema, @SynonymName, @SynonymSchema;

-- Loop through the cursor until there are no more rows
WHILE @@FETCH_STATUS = 0
BEGIN
    -- Declare a variable to store the create synonym statement for each table if it does not exist already
    DECLARE @CreateSynonymStatement NVARCHAR(MAX);
    SET @CreateSynonymStatement = 'IF NOT EXISTS (SELECT 1 FROM sys.synonyms WHERE name = ''' + @SynonymName + ''' AND schema_id = SCHEMA_ID(''' + @SynonymSchema + '''))'
    SET @CreateSynonymStatement = @CreateSynonymStatement + 'BEGIN'
    SET @CreateSynonymStatement = @CreateSynonymStatement + '  CREATE SYNONYM ' + QUOTENAME(@SynonymSchema) + '.' + QUOTENAME(@SynonymName) + ' FOR ' + QUOTENAME(@TableSchema) + '.' + QUOTENAME(@TableName) + ';';
    SET @CreateSynonymStatement = @CreateSynonymStatement + 'END'

    -- Execute the create synonym statement using dynamic SQL
    EXEC sp_executesql @CreateSynonymStatement;

    -- Fetch the next row
    FETCH NEXT FROM TableSynonymCursor INTO @TableName, @TableSchema, @SynonymName, @SynonymSchema;
END

-- Close and deallocate the cursor
CLOSE TableSynonymCursor;
DEALLOCATE TableSynonymCursor;