If you need to create a table on a linked server, you can do this either directly on the remote server, or you can do it by running a script from your local server.

Here are two ways of using T-SQL to create a table on a linked server.

Both methods use the EXECUTE/EXEC statement. But each uses a different approach.

Prerequisite

Executing stored procedures on a linked server require that you enable RPC Out (if it isn’t already enabled).

You can check if RPC Out is enabled by querying the sys.servers catalog view.

The following code enables RPC Out on a linked server called Homer:

EXEC sp_serveroption 'MyLinkedServer', 'rpc out', 'true';

With RPC Out enabled, we can go ahead and create the tables.

Method 1

Our first approach is to use the AT argument to specify a linked server for which to run the code. This syntax allows us to send pass-through commands to linked servers.

It goes like this:

EXEC ('
    USE  [Pets];
    CREATE TABLE [dbo].[Cats](
        [CatId] [int] IDENTITY(1,1) NOT NULL,
        [CatName] [varchar](60) NULL
    ) ON [PRIMARY];
') AT Homer;

This creates a table called Cats in the Pets database on the linked server called Homer.

This code obviously assumes that there’s a database called Pets on the linked server. If there isn’t, you’ll need to create it first.

The linked server name (Homer in this case) is an existing linked server definition in the local server. It’s not the name of the actual remote server.

Method 2

Our second approach is to execute the sp_executesql system stored procedure on the remote server, while passing in our T-SQL statement.

Like this:

EXEC Homer.master.dbo.sp_executesql N'
    USE  [Pets];
    CREATE TABLE [dbo].[Dogs](
        [DogId] [int] IDENTITY(1,1) NOT NULL,
        [DogName] [nvarchar](255) NULL,
        [GoodDog] [bit] NULL
    ) ON [PRIMARY];
    ';

So even though the syntax is different, the result is the same. Plus, the CREATE TABLE syntax doesn’t change, regardless of the method used to access the linked server.

Pass the Statement as a Variable

You can also pass the CREATE TABLE statement as a variable to the EXEC statement.

This can be useful if you’ve got a lot of tables and/or other objects to create.

Here’s an example using the first syntax:

DECLARE @tsql nvarchar(max);
SET @tsql = '
USE  [Pets];
CREATE TABLE [dbo].[Cats](
	[CatId] [int] IDENTITY(1,1) NOT NULL,
	[CatName] [varchar](60) NULL
) ON [PRIMARY];
CREATE TABLE [dbo].[Dogs](
	[DogId] [int] IDENTITY(1,1) NOT NULL,
	[DogName] [nvarchar](255) NULL,
	[GoodDog] [bit] NULL
) ON [PRIMARY];
';
EXEC (@tsql) AT Homer;

And here’s the equivalent using the second syntax:

DECLARE @tsql nvarchar(max);
SET @tsql = '
USE  [Pets];
CREATE TABLE [dbo].[Cats](
	[CatId] [int] IDENTITY(1,1) NOT NULL,
	[CatName] [varchar](60) NULL
) ON [PRIMARY];
CREATE TABLE [dbo].[Dogs](
	[DogId] [int] IDENTITY(1,1) NOT NULL,
	[DogName] [nvarchar](255) NULL,
	[GoodDog] [bit] NULL
) ON [PRIMARY];
';
EXEC Homer.master.dbo.sp_executesql @tsql;