locked
CREATE TABLE SCRIPT IN VARIOUS ENVIRONMENT RRS feed

  • Question

  • I am looking for a create table script that checks for each environment and executes the create table statement in single script.

    I have 3 environments DEV/STG/PROD

    Friday, January 17, 2020 1:07 AM

Answers

  • I am looking for a create table script that checks for each environment and executes the create table statement in single script.

    I have 3 environments DEV/STG/PROD

    You can use SSMS > Registered servers > Central management server. Register your 3 servers and using new query option you can run it in all 3 servers. Details here https://www.sqlshack.com/how-to-run-multiple-queries-using-the-central-management-server/

    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    • Marked as answer by Abhinav530 Friday, January 17, 2020 1:56 PM
    Friday, January 17, 2020 5:21 AM

All replies

  • Try below steps

    1. Create Linked server for other two servers (STG , PROD) in a server (DEV)

    2. Loop through below query for all the three servers , replace DEV with other two STG and PROD

     IF OBJECT_ID('DEV.database.dbo.sp_GetRoutingArea', 'T') IS NULL

    Create TableName

    (co;l1 varchar(10))

    Friday, January 17, 2020 2:18 AM
  • Hi Abhinav530,

    There is a direct way: 

    1. Set up the correct credentials in one environment (like Dev) for the linked server (STG and PROD), defaulting to Anonymous.

    2. Create the table in the environment and right click Script Table As.

    3. select * into targetTable from [sourceserver].[sourcedatabase].[dbo].[sourceTable]

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com


    • Edited by Lily Lii Friday, January 17, 2020 3:18 AM
    Friday, January 17, 2020 3:08 AM
  • I am looking for a create table script that checks for each environment and executes the create table statement in single script.

    I have 3 environments DEV/STG/PROD

    You can use SSMS > Registered servers > Central management server. Register your 3 servers and using new query option you can run it in all 3 servers. Details here https://www.sqlshack.com/how-to-run-multiple-queries-using-the-central-management-server/

    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    • Marked as answer by Abhinav530 Friday, January 17, 2020 1:56 PM
    Friday, January 17, 2020 5:21 AM